Dec 01 2022 05:10 AM
I have large arrays of text that I am trying to use a macro on to save time. The goal is to change a reference in the formula as bolded below:
=xlookup($B$23,lookup_array,xlookup(A23,lookup_array,return_array))
I need to reference this to the same cell for each section of data.
I record a macro where I change the reference, make it an absolute reference (by hitting F4), and then auto fill all the way down. This works as intended. When I try to run the Macro in the next section the formula refers to the original cell I referenced when recording the Macro. I need this to refer to the cell R-1C-2 and then autofill this down as an absolute reference. I hope this makes sense. See table below for an induction of what I'm trying to achieve.
Note that I am using relative while recording the Macro.
Any help would be appreciated.
Dec 01 2022 06:28 AM
Try this:
Sub UpdateFormulas()
Dim rng1 As Range
Dim rng2 As Range
Dim adr1 As String
Dim adr2 As String
Set rng1 = Range("B:B").Find(What:="*")
If Not rng1 Is Nothing Then
adr1 = rng1.Address
Do
adr2 = rng1.Address
Set rng2 = rng1.Offset(1, 2)
Range(rng2, rng2.End(xlDown)).Replace What:="$B$1", Replacement:=adr2, LookAt:=xlPart
Set rng1 = Range("B:B").Find(What:="*", After:=rng1)
If rng1 Is Nothing Then Exit Do
Loop Until rng1.Address = adr1
End If
End Sub