add found cell address to formula

[seekker2008]

hi there, can someone pls help. I'm trying to add found cell address to a formula but can't get it to work
my code so far

Dim LL
l = Application.Match("Delivery", Range("1:1"), 0)
If IsError(l) Then
On Error Resume Next
End If
Columns(l).Select
LL = Cells(2, ActiveCell.Column)
'LL = Columns(L).Cells(2, 1).Address(0, 0)
'Range("aa1") = LL
Dim pp
p = Application.Match("Movement Type", Range("1:1"), 0)
If IsError(p) Then
On Error Resume Next
End If
Columns(p).Select
pp = Cells(2, ActiveCell.Column)
Range("S2").Select
ActiveCell.Formula = "=sum(ll.address & pp.address)"

 

[Yongle]

I am not sure of the formula you were trying to create
- perhaps somethng like = Sum("C2:F2")

Range.Find returns a Range object (ie a cell)
.Column returns the column number of that cell
.Address returns absolute cell ref (example $A$1) as a String
.Address(0,0) returns relative cell ref (example A1)
Message box added so that you can see the values returned
Note how errors are handled
- On Error Resume Next goes above line where error expected
(tells VBA to keep going)
- If P = 0 Or L = 0 Then GoTo Handling tells VBA what to do if error was encountered
(here Long variable retains its default value of Zero, so that used as condition)

Try this

Sub CreateFormula()

    Dim LL      As String, L    As Long
    Dim PP      As String, P    As Long
    Dim fStr    As String
    On Error Resume Next
    
    L = Range("1:1").Find("Delivery", LookAt:=xlWhole).Column
    LL = Cells(2, L).Address(0, 0)
    
    P = Range("1:1").Find("Movement Type", LookAt:=xlWhole).Column
    PP = Cells(2, P).Address(0, 0)
    fStr = "=sum(" & LL & ":" & PP & ")"
    
    If P = 0 Or L = 0 Then GoTo Handling
    Range("S2").Formula = fStr
    
    Const v = vbCr
    MsgBox "L= " & L & v & "LL= " & LL & v & "P= " & P & v & "PP= " & PP & v & fStr, vbInformation, "For Testing Only"
    Exit Sub
Handling:
MsgBox "Delivery is column " & L & vbCr & "Movement Type is column " & P, vbExclamation, "ERROR"
End Sub

If it is not what you want let me know expected formula in S2

 

[Yongle]

Using MATCH it looks like this

Sub CreateFormulaUsingMatch()

    Dim LL      As String, L    As Long
    Dim PP      As String, P    As Long
    Dim fStr    As String
    On Error Resume Next
    
    L = WorksheetFunction.Match("Delivery", Range("1:1"), 0)
    LL = Cells(2, L).Address(0, 0)
    
    P = WorksheetFunction.Match("Movement Type", Range("1:1"), 0)
    PP = Cells(2, P).Address(0, 0)
    fStr = "=sum(" & LL & ":" & PP & ")"
    
    If P = 0 Or L = 0 Then GoTo Handling
    Range("S2").Formula = fStr
    
    Const v = vbCr
    MsgBox "L= " & L & v & "LL= " & LL & v & "P= " & P & v & "PP= " & PP & v & fStr, vbInformation, "For Testing Only"
    Exit Sub
Handling:
MsgBox "Delivery is column " & L & vbCr & "Movement Type is column " & P, vbExclamation, "ERROR"
End Sub

For your future postings
Using CODE TAGS makes your code more readable
- makes it look similar to how it looks in VBA window

Click on # icon above post window and then paste code between the tags
[ CODE ] - paste code here - [ /CODE ]

 

[Yongle]

Perhaps somethng like = Sum("C2:F2")

OOPS just spotted a minor typo in my first post
The code is fine but an Excel formula does not require " " and would be = Sum(C2:F2)

 

[seekker2008]

OOPS just spotted a minor typo in my first post
The code is fine but an Excel formula does not require " " and would be = Sum(C2:F2)

Hi Yongle, thanks for replying. Your code works. I've a report which have thousand of rows. Depending on who runs the report, the position of the headers are not always in the same column, Delivery might be in cloumn L from person A and in column C from person B
From the report created, I will need to do the following formulas, concatenate, countif, sumif and vlookup and autofill down across
Then from there, a pivot table has to be created and the summary sent to all parties concerned

eg one of the formula

=IF(OR(I2="601",I2="631",I2="641",I2="643"),CONCATENATE(A2,B2,L2),CONCATENATE(A2,B2,K2))

 

[seekker2008]

Hi Yongle - correct formula shown in MsgBox but nothing is showing in cell S2. Can you pls help

Sub CreateFormula()

    Dim AA      As String, A    As Long
    Dim BB      As String, B    As Long
    Dim KK      As String, K    As Long
    Dim LL      As String, L    As Long
    Dim II      As String, I    As Long
    Dim PP      As String, P    As Long
    Dim SS      As String, S    As Long
    Dim fStr    As String
    Dim fStr2    As String
    Dim fStr3    As String
    Dim fStr4    As String
    Dim fStr5    As String
    On Error Resume Next
    
    A = Range("1:1").Find("Plant", LookAt:=xlWhole).Column
    AA = Cells(2, A).Address(0, 0)


    B = Range("1:1").Find("Storage Location", LookAt:=xlWhole).Column
    BB = Cells(2, B).Address(0, 0)


    L = Range("1:1").Find("Delivery", LookAt:=xlWhole).Column
    LL = Cells(2, L).Address(0, 0)


    K = Range("1:1").Find("Material Document", LookAt:=xlWhole).Column
    KK = Cells(2, K).Address(0, 0)


    I = Range("1:1").Find("Movement Type", LookAt:=xlWhole).Column
    II = Cells(2, I).Address(0, 0)


    S = Range("1:1").Find("Joined", LookAt:=xlWhole).Column
    SS = Cells(2, S).Address(0, 0)
    


    fStr = "=CONCATENATE(" & AA & "," & BB & "," & LL & ")"
    fStr3 = "CONCATENATE(" & AA & "," & BB & "," & LL & ")"
    fStr4 = "CONCATENATE(" & AA & "," & BB & "," & KK & ")"
    fStr5 = "=IF(OR(" & II & "=" & "601" & "," & II & "=" & "631" & "," & II & "=" & "641" & "," & II & "=" & "643" & ")"
    fStr2 = fStr5


    Range("S2").Formula = fStr5 & "," & fStr3 & "," & fStr4


    
    Const v = vbCr
    MsgBox fStr5 & "," & fStr3 & "," & fStr4, vbInformation, "For Testing Only"


    Exit Sub
End Sub

 

[Yongle]

There may be a minor issue with your formula

Add this line

Debug.Print fStr5 & "," & fStr3 & "," & fStr4

above

Range("S2").Formula = fStr5 & "," & fStr3 & "," & fStr4

Debug.Print returns a value to the Immediate Window (if you canot see that window it make it visible in VBA with {CTRL} G )
(Copy and paste whatever is returned into cell S2 if necessary to spot what is wrong and) fix the string in the code and test again

You can delete the final line Exit Sub if you are not using error handling

 

[seekker2008]

Hi Yongle, sorry for the late reply.
Have been busy with another report
Been doing a few changes to the line and manage to get it working

 Range("S2").Formula = fStr5 & "," & fStr3 & "," & fStr4 & ")"

the formula was just missing & ")" at the end of the code
Thanks for helping out a newbie