VBA formula without using RC[-x] designation

[wingfield]

hello,

I have a spreadsheet with say 6 columns. off to the right, I'm creating a formula in say cell m2, then copying that formula down until the end of the data. the formula in m2 would be something like a concatenate formula that uses two columns, say column b and column c. currently, my formula in m2 looks something like this:

ActiveCell.FormulaR1C1 = "=CONCATENATE(""I"",RC[-9])"

this example uses only one column and a hardcoded "I", but you get the picture.

my question is this. is there a way I can avoid using the RC[-x] format? I have column headings. is there a way the formula can simply look for the column heading to determine which cell to use at the top of that column and then copy it down?

don't worry about the copy down part. I can handle that. I'm simply looking for a way to get that first formula in cell m2 to use something other than those RC[-x] format and something more "permanent".

hopefully, this makes sense?

best regards,

david

 

[Momentman]

If you don't want to use the R1C1 reference, then something like this

 ActiveCell.Formula = "=Concatenate(K1,C1)"

The formula will concatenate cells K1 and C1 for example

Add: To find a particular column header, you probably need to use the Range. Find method

 

[Andrew Poulsom]

What's the column heading you want to look for?

 

[wingfield]

What's the column heading you want to look for?

I've got around six columns;but one example is persfamID. others are eventdate, eventdatetr, eventplace.
​

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim Col As Long
    With ActiveSheet
        Col = .Rows(1).Find(What:="persfamID", After:=.Cells(1, .Columns.Count), LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
    End With
    ActiveCell.FormulaR1C1 = "=CONCATENATE(""I"",RC" & Col & ")"
End Sub

 

[wingfield]

Example:

Sub Test()
    Dim Col As Long
    With ActiveSheet
        Col = .Rows(1).Find(What:="persfamID", After:=.Cells(1, .Columns.Count), LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
    End With
    ActiveCell.FormulaR1C1 = "=CONCATENATE(""I"",RC" & Col & ")"
End Sub

thank you very much sir!!