Forum Discussion

malttbn's avatar
malttbn
Copper Contributor
Dec 01, 2022

Issue with autofill and absolute references while using a Macro

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. 

excel
Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 01, 2022

    malttbn 

    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

Resources