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...
excel
Macros and VBA
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