Require Syntax for Worksheet and column reference after the Column Name Searched by Header

[SamDsouza]

Hello
What would be the correct Syntax for Column reference when searched by header

Public Sub FormulaWorking()

Dim wsInv as Worksheet
Dim wsMis As Worksheet
Dim SrchtxtStr As String

Set wsMis = Worksheets("MIS")

Set wsInv = Worksheets("InvSheet")

With wsInv.Range("A1:Z1")

SrchtxtStr = "Riveria"

Set cfind = .Find(What:=SrchtxtStr, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not cfind Is Nothing Then
        MsgBox SrchtxtStr & vbCrLf & "Is in Col. No " & cfind.Column & "   Row No. " & cfind.Row
     Else
        MsgBox "Not Found in Range"
      
End If
End With

With wsMis
    .Range("CL3" & ":CL" & lastRow).Formula = "=SUMIFS('InvSheet'!S:S,'InvSheet'!A:A,MIS!A:A,'InvSheet'!K:K,MIS!$CL$1)"
End With
End Sub

'After searching the column name by header name ie i searched the column as "S" as per above formula
'So as per above formula How to have correct syntax for
'' .Range("CL3" & ":CL" & lastRow).Formula = "=SUMIFS('InvSheet'!cfind.Column , 'InvSheet'!A:A,......)
'' And
'' .Range("CL3" & ":CL" & lastRow).Formula = "=SUMIFS(wsInv.cfind.Column , wsInv.column ("A:A").....

Your Help will be highly appreciated
SamD

 

[FormR]

Hi, you could try something like this.

With wsMis
    .Range("CL3" & ":CL" & lastrow).Formula = "=SUMIFS('InvSheet'!" & Columns(cfind.Column).Address & ",'InvSheet'!A:A,MIS!A:A,'InvSheet'!K:K,MIS!$CL$1)"
End With

 

[SamDsouza]

Thank you FormR. Indeed it was great input

With wsMis
    .Range("CL3" & ":CL" & lastrow).Formula = "=SUMIFS('InvSheet'!" & Columns(cfind.Column).Address & ",'InvSheet'!A:A,MIS!A:A,'InvSheet'!K:K,MIS!$CL$1)"
End With

As this is related to syntax
and worksheet is already defined or SET
Can I
.Formula = "=SUMIFS(wsInv.Columns(cfind.Column).Address), (wsInv.Columns(cfind.Column).Address), (wsMIS. Columns("A:A").Address) .......


Thanks Once again
SamD

 

[FormR]

One way to get the worksheet name from your variable would be like this.

With wsMis
   .Range("CL3" & ":CL" & lastrow).Formula = "=SUMIFS('" & wsInv.Name & "'!" & Columns(cfind.Column).Address & ",'InvSheet'!A:A,MIS!A:A,'InvSheet'!K:K,MIS!$CL$1)"
End With

And the same sort of thing for the other parts of the formula.

 

[SamDsouza]

Thanks FormR but am really sorry to bother you for petty lssues

I get error 1004 Application defined.... Error
because i wanted to use in the below manner though above posts are with correct syntax if not used with the Columns property
Any ideas how can i use correctly with Columns("A:A").Address or have i gone wrong using & wsMis.Range("CL1").Value & "$)" at end of formula

.Range("CL3" & ":CL" & lastRow).Formula = "=SUMIFS('" & wsInv.Name & "'!" & Columns(colFind.Column).Address & ",'" & _
wsInv.Name & "'!" & Columns("A:A").Address & wsMis.Name & "'!" & Columns("A:A").Address & ",'" & _
wsInv.Name & "'!" & Columns("K:K").Address & wsMis.Name & "'!$" & wsMis.Range("CL1").Value & "$)"

SamD

 

[SamDsouza]

Guys Corrected
Corrections in bold
.Range("CL3" & ":CL" & lastRow).Formula = "=SUMIFS('" & wsInv.Name & "'!" & Columns(colFind.Column).Address(0,0) & ",'" & _
wsInv.Name & "'!" & Columns("A").Address (0,0) & ","& wsMis.Name & "'!" & Columns("A").Address (0,0) & ",'" & _
wsInv.Name & "'!" & Columns("K").Address (0,0) & "," & wsMis.Name & "'!" & wsMis.Range("CL1").Address & ")"

SamD

 

[FormR]

& Columns("A").Address (0,0) &

Hi, this doesn't make sense to me, it will always return "A:A" so why not just use that directly?

Same for column K and cell CL1.

Not tested, but for example.

"=SUMIFS('" & wsInv.Name & "'!" & Columns(colFind.Column).Address(0, 0) & ",'" & _
wsInv.Name & "'!A:A,'" & wsMis.Name & "'!A:A,'" & wsInv.Name & "'!K:K,'" & wsMis.Name & "'!CL1)"

 

[SamDsouza]

Oh OK thanks FormR for the clarification