Excel VBA to replace value with same value + 1

[ExcelDropper]

I have a cell that is =vlookup(a1,a:c,2,0) but over and over again like =vlookup(a1,a:c,2,0)+vlookup(a1,a:c,2,0)+vlookup(a1,a:c,2,0)+vlookup(a1,a:c,2,0). Is there a fast way to make this lookup value increase by 1 every time it is repeated? so it would be vlookup(a1...)+vlookup(a2...)+vlookup(a3...) using VBA?

 

[offthelip]

this will do it 10 times , just change the loop as you require:

Sub tst()
'=vlookup(a1,a:c,2,0)+
txt = ""
pls = ""
For i = 1 To 10
 txt = txt & pls & "vlookup(a" & i & ",a:c,2,0)"
 pls = "+"
Next i
Cells(5, 5) = "=" & txt
End Sub

 

[EXCEL MAX]

Hello Excel brother,
Can you explain problem a little bit better.
It's not clear are you want textual increasing like Offthelip have done,
or you want range result increasing. As second solution try this...

Sub IncreaseFormula()

    Dim varWorksheet As Worksheet
    Dim varRange As Range
    Dim varNLoop As Long
    Dim varResult
    
    Set varWorksheet = ActiveSheet
    Set varRange = varWorksheet.Range("A1:C5")
    
    For varNLoop = 1 To varRange.Rows.Count
        varResult = Application.WorksheetFunction.VLookup _
            (ActiveSheet.Range("A" & varNLoop), varRange, 2, 0)
        MsgBox (varResult)
    Next
   
End Sub

 

[ExcelDropper]

Hello Excel brother,
Can you explain problem a little bit better.
It's not clear are you want textual increasing like Offthelip have done,
or you want range result increasing. As second solution try this...

Sub IncreaseFormula()

    Dim varWorksheet As Worksheet
    Dim varRange As Range
    Dim varNLoop As Long
    Dim varResult
   
    Set varWorksheet = ActiveSheet
    Set varRange = varWorksheet.Range("A1:C5")
   
    For varNLoop = 1 To varRange.Rows.Count
        varResult = Application.WorksheetFunction.VLookup _
            (ActiveSheet.Range("A" & varNLoop), varRange, 2, 0)
        MsgBox (varResult)
    Next
  
End Sub

Thank you for the solution, unfortunately I can't quite get it to work. What I am looking to do here is change already existing formulas, which is why offthelip's solution wouldn't work. By this I mean I already have a formula, lets say in C3 of my document, that goes like this: =VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A1,H8:I8,2,0). I am happy with every part of this formula aside from the A1 which is repeating itself. For each time it repeats itself, I simply want it to increase by 1. So the end result would be =VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A2,H8:I8,2,0)+VLOOKUP(A2,H8:I8,2,0)+VLOOKUP(A2,H8:I8,2,0). The reason I can't use the first solution is because that simply inputs a new formula. I want to adjust the pre-existing formula. In my actual document, there will be many of these vlookups with different search ranges. I am already happy with the search ranges. I just want to change the A1 spam, which they will all have. What I am looking for is some sort of macro where I can input a range, let's say A1:C5, and replace all instances of these repeating A1s to become A1, A2, A3 and so on.

 

[ExcelDropper]

Hello Excel brother,
Can you explain problem a little bit better.
It's not clear are you want textual increasing like Offthelip have done,
or you want range result increasing. As second solution try this...

Sub IncreaseFormula()

    Dim varWorksheet As Worksheet
    Dim varRange As Range
    Dim varNLoop As Long
    Dim varResult
   
    Set varWorksheet = ActiveSheet
    Set varRange = varWorksheet.Range("A1:C5")
   
    For varNLoop = 1 To varRange.Rows.Count
        varResult = Application.WorksheetFunction.VLookup _
            (ActiveSheet.Range("A" & varNLoop), varRange, 2, 0)
        MsgBox (varResult)
    Next
  
End Sub

Just to clarify, imagine I have a formula in C1 that says =VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A1,H8:I8,2,0). In C2 I have another formula that is =VLOOKUP(A1,B3:B3,3,0)+VLOOKUP(A1,B3:B3,3,0)+VLOOKUP(A1,B3:B3,3,0)+VLOOKUP(A1,B3:B3,3,0). All I want to do is be able to input the range C1:C2 into a macro and have C1 change to =VLOOKUP(A1,H8:I8,2,0)+VLOOKUP(A2,H8:I8,2,0)+VLOOKUP(A3,H8:I8,2,0)+VLOOKUP(A4,H8:I8,2,0) while C2 changes to =VLOOKUP(A1,B3:B3,3,0)+VLOOKUP(A2,B3:B3,3,0)+VLOOKUP(A3,B3:B3,3,0)+VLOOKUP(A4,B3:B3,3,0). The entire formula will stay the same, apart from the A1 part.

 

[EXCEL MAX]

Ok, now I understand , wait me to make macro.

 

[EXCEL MAX]

Here it is.
It's not some kind of smooth procedure, but can done job.

Option Explicit

Sub ChangeFormula()
    
    Dim varWorksheet As Worksheet
    Dim varRange1, varRange2 As Range
    Dim varTempString, varTempString2, varTempString3, varTempString4, _
        varTempStringLeft, varTempStringMiddle, varTempStringRight As String
    Dim varNLoop, varNLoops, varSpace1, varSpace2, varChar As Integer
  
    Set varWorksheet = ActiveSheet
    Set varRange2 = varWorksheet.Range("C1:C2")
    
    For Each varRange1 In varRange2
       varTempString = varRange1.Formula
       varNLoops = (Len(varTempString) - Len(Replace(varTempString, "+", ""))) + 1
       varTempString2 = varTempString
       For varNLoop = 1 To varNLoops
            varSpace1 = InStr(1, varTempString, "A1")
            varSpace2 = InStr(1, varTempString, ",")
            varChar = varSpace2 - varSpace1
            varTempString3 = Left(varTempString2, varSpace1 + varSpace2)
            varTempStringLeft = Mid(varTempString3, 2, varSpace1 - varChar)
            varTempStringMiddle = "A" & varNLoop
            varTempStringRight = Mid(varTempString3, varSpace1 + _
                varChar, Len(varTempString3) - (varSpace1 + varChar - 1))
            varTempString4 = varTempString4 & varTempStringLeft & _
                varTempStringMiddle & varTempStringRight & "+"
       Next
       varTempString4 = Mid(varTempString4, 1, Len(varTempString4) - 1)
       varRange1.Formula = "=" & varTempString4
       varTempString4 = ""
     Next
     MsgBox ("You are successfully changed the ranges.")
     
End Sub

 

[ExcelDropper]

Here it is.
It's not some kind of smooth procedure, but can done job.

Option Explicit

Sub ChangeFormula()
   
    Dim varWorksheet As Worksheet
    Dim varRange1, varRange2 As Range
    Dim varTempString, varTempString2, varTempString3, varTempString4, _
        varTempStringLeft, varTempStringMiddle, varTempStringRight As String
    Dim varNLoop, varNLoops, varSpace1, varSpace2, varChar As Integer
 
    Set varWorksheet = ActiveSheet
    Set varRange2 = varWorksheet.Range("C1:C2")
   
    For Each varRange1 In varRange2
       varTempString = varRange1.Formula
       varNLoops = (Len(varTempString) - Len(Replace(varTempString, "+", ""))) + 1
       varTempString2 = varTempString
       For varNLoop = 1 To varNLoops
            varSpace1 = InStr(1, varTempString, "A1")
            varSpace2 = InStr(1, varTempString, ",")
            varChar = varSpace2 - varSpace1
            varTempString3 = Left(varTempString2, varSpace1 + varSpace2)
            varTempStringLeft = Mid(varTempString3, 2, varSpace1 - varChar)
            varTempStringMiddle = "A" & varNLoop
            varTempStringRight = Mid(varTempString3, varSpace1 + _
                varChar, Len(varTempString3) - (varSpace1 + varChar - 1))
            varTempString4 = varTempString4 & varTempStringLeft & _
                varTempStringMiddle & varTempStringRight & "+"
       Next
       varTempString4 = Mid(varTempString4, 1, Len(varTempString4) - 1)
       varRange1.Formula = "=" & varTempString4
       varTempString4 = ""
     Next
     MsgBox ("You are successfully changed the ranges.")
    
End Sub

TY for this! i really appreciate the effort that went into putting a macro together for something so strange lol. Seems to be working perfectly.
Much appreciated again, will save me a ton of time in the upcoming years

 

[EXCEL MAX]

Ok Excel brother,
I'm glad that we are create useful tool.