CODEWITHSUNDEEP
vbaexcel
Nick
Nick

Reputation: 43

Adding formula in Created column which you do not know the column value

I have performed a find function in Excel that looks for a specific value and inserts a new column 2 places to the right, As this value can be anywhere, I am not sure of the column value(A,B,C,D,etc.) How do I add a formula to this newly created column if I am unsure of the value?

This is what I have so far: 

 With Range("A1:Z1")
    Set rFind = .Find(What:="US", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        lastColumn = rFind.Column
    Columns(lastColumn + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range((lastColumn + 2) & LR).Formula = "=(5)"
    End If

Upvotes: 0

Views: 47

Answers (2)

TomJohn
TomJohn

Reputation: 747

My answer:

Sub test()

With Range("A1:Z1")
    Set rFind = .Find(What:="US", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        lastColumn = rFind.Column
    Columns(lastColumn + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(Cells(1, (lastColumn + 2)), Cells(123, (lastColumn + 2))).Formula = "=(5)"
    End If
End With

End Sub

Replace 123 with last row number.

Per request for multiple conditions finding (requieres that "US" and "New Price" are in the same column):

Sub test()

    lastColumn = Evaluate(" Match(""US"" & ""New Price"", A1:Z1 & A2:Z2, 0)")
    Columns(lastColumn + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(Cells(1, (lastColumn + 2)), Cells(123, (lastColumn + 2))).Formula = "=(5)"

End Sub

In case of described issue:

Sub test2()

Dim rng As Range

With Range("A1:Z1")
    Set rFind = .Find(What:="US", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        lastColumn = rFind.Column
    End If
End With

Set rng = Range(Cells(2, lastColumn), Cells(2, lastColumn + 7))

final_Column = Application.Match("New Price", rng, 0)

lastColumn = lastColumn + final_Column

Columns(lastColumn + 2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(Cells(1, (lastColumn + 2)), Cells(123, (lastColumn + 2))).Formula = "=(5)"


End Sub

Upvotes: 1

CLR
CLR

Reputation: 12289

The following will put that formula (in the example it would go in column F), referring back to column D for the lookup key:

rFind.Offset(0, 2).Formula = "=VLOOKUP(RC[-2],'[ABC.xlsx]Sheet1'!C1,1,FALSE)"

PS. C1 is not much of a table to perform a VLOOKUP on but okay..

Upvotes: 0

Related Questions