Calling HeaderName to find its respective column with Set = .find but when Column Searched The same column reference is overlapped in a Formula. How t

[NimishK]

Hello
I’ve created a Procedure to call For Header to find its respective column with Set = .find
Firstly I created the following

Public Sub ColHeaderFindPos()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim AmtStr As String,  cgst12Str As String, sgst12Str As String, cgst14Str As String, Sgst14str As String
Dim AmtColFind As Range, cgst12ColFind As Range, sgst12ColFind As Range, cgst12ColFind As Range, sgst14ColFind As Range

Set ws1 = Worksheets("Sheet3")
Set ws2 = Worksheets("Sheet4")

AmtStr = “Amount”
cgst12Str = ”CGST 12%
sgst12Str =” SGST 12%
cgst14Str = ”CGST 14%
sgst14Str =” SGST 14%

Set amtColFind = .Find(What:= AmtStr, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set cgst12ColFind = .Find(What:= cgst12Str, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set sgst12ColFind = .Find(What:= sgst12Str, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set cgst14ColFind = .Find(What:= cgst14Str, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set sgst14ColFind = .Find(What:= cgst14Str, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)


With ws1
      .Range("L2" & ":L" & lastRow).Formula = "=SUMIFS('" & ws2.Name & "'!" & Columns(amtColFind.Column).Address(0, 0) & ",'" & _
ws2.Name & "'!" & Columns("A").Address(0, 0) & "," & ws1.Name & "!" & Columns("A").Address(0, 0) & ",'" & ' ws2.Name & "'!" & Columns("K").Address(0, 0) & "," & ws1.Name & "!" & ws1.Range("L1").Address & ")"

.Range("M3" & ":M" & lastRow).Formula = "=SUMIFS('" & ws2.Name & "'!" & Columns(cgst12ColFind.Column).Address(0, 0) & ",'" & _
ws2.Name & "'!" & Columns("A").Address(0, 0) & "," & ws1.Name & "!" & Columns("A").Address(0, 0) & ",'" &  ws2.Name & "'!" & Columns("K").Address(0, 0) & "," & ws1.Name & "!" & ws1.Range("CL1").Address & ")" &  "+SUMIFS" & "('" & ws2.Name & "'!" & Columns(sgst12ColFind.Column).Address(0, 0) & ",'" & ws2.Name & "'!" & Columns("A").Address(0, 0) & "," &  ws1.Name & "!" & Columns("A").Address(0, 0) & ",'" & _
ws2.Name & "'!" & Columns("K").Address(0, 0) & "," & ws1.Name & "!" & ws1.Range("L1").Address & ")"
End With
End Sub

InOrder to avoid Following Lines Marked in Red
Then I Created following procedure with Array to Call

Sub colNameHdrFindArray(ByVal HeaderName As String, ByRef columnRef As Variant)

    Dim ws1 As Worksheet,  ws2 As Worksheet, HeaderArrayNames As Variant, colfind As Range
    Dim i As Long
    Set wKS = ActiveWorkbook.Worksheets("Sheet1")
    i = 1

    HeaderArrayNames = Array("Amount", "CGST 12%", "SGST 12%", "CGST 14%", "SGST 14%", "Contract ID", "POS")

    With ws1.Range("A1:J1")
     For i = LBound(HeaderArrayNames) To UBound(HeaderArrayNames)

           Set colfind = .Find(What:=HeaderArrayNames(i), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

              If Not colfind Is Nothing Then
                   If HeaderName = HeaderArrayNames(i) Then
                      MsgBox HeaderName & vbCrLf & "Is in Col. No " & colfind.Column                        
                      columnRef = colfind.Column
                     Exit For
                 End If
                Else
                     MsgBox "Not Found in Range"
                   End If
         Next
End With
End Sub

Public Sub ColHeaderFindPos()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim AmtStr As String,  cgst12Str As String, sgst12Str As String, cgst14Str As String, Sgst14str As String
Dim AmtColFind As Range, cgst12ColFind As Range, sgst12ColFind As Range, cgst12ColFind As Range, sgst14ColFind As Range

Set ws1 = Worksheets("Sheet3")
Set ws2 = Worksheets("Sheet4")

With ws1

Call colNoSheetNameHdrRow("Amount", columnRef) 

      .Range("L2" & ":L" & lastRow).Formula = "=SUMIFS('" & ws2.Name & "'!" & Columns(columnRef).Address(0, 0) & ",'" & _

ws2.Name & "'!" & Columns("A").Address(0, 0) & "," & ws1.Name & "!" & Columns("A").Address(0, 0) & ",'" & ' ws2.Name & "'!" & Columns("K").Address(0, 0) & "," & ws1.Name & "!" & ws1.Range("L1").Address & ")"

Call colNoSheetNameHdrRow("CGST 12%", columnRef)
Call colNoSheetNameHdrRow("SGST 12%", columnRef)

.Range("M3" & ":M" & lastRow).Formula = "=SUMIFS('" & ws2.Name & "'!" & Columns(columnRef).Address(0, 0) & ",'" & _

ws2.Name & "'!" & Columns("A").Address(0, 0) & "," & ws1.Name & "!" & Columns("A").Address(0, 0) & ",'" &  ws2.Name & "'!" & Columns("K").Address(0, 0) & "," & ws1.Name & "!" & ws1.Range("L1").Address & ")" &  "+SUMIFS" & "('" & ws2.Name & "'!" & Columns(columnRef).Address(0, 0) & ",'" & ws2.Name & "'!" & Columns("A").Address(0, 0) & "," &  ws1.Name & "!" & Columns("A").Address(0, 0) & ",'" & _
ws2.Name & "'!" & Columns("K").Address(0, 0) & "," & ws1.Name & "!" & ws1.Range("L1").Address & ")"

End With
End Sub

So which Columns(columnRef).Address(0, 0) is refered to when
Call colNoSheetNameHdrRow("CGST 12%", columnRef)
Call colNoSheetNameHdrRow("SGST 12%", columnRef)

How can the above be coded for calling a procedure with Headername and its respective column
Call colNoSheetNameHdrRow("CGST 12%", cgst12columnRef)
Call colNoSheetNameHdrRow("SGST 12%", sgst12columnRef)

So when using column reference can use as follows

Columns(cgst12columnRef).Address(0, 0)
Columns(sgst12columnRef).Address(0, 0)

Thanks in Advance ?

NimishK

 

[NimishK]

Finally resolved FYI I've completely change the logic by not using Set = .Find method
The below MrExcel thread gave rather clear picture for what i wanted
Creating a Procedure to Call HeaderName but no success to call a procedure for HeaderName’s Column Position

NimishK