VBA Get column letter based on cell text and use as string

[adinev]

Hi all,
I am trying to find a column letter based on a text ,contained in a cell. I managed to find the letter, but the next part is what bothers me. The column letter(declared as "trcol") is a part of a loop that goes through the table and copies certain values based on that column.So that column should be the target column to copy from.This is what i have so far:

Dim trcol as String
Workbooks.Open (ThisWorkbook.Path & workb)
Sheets(sh).Select
Set Cell = Cells.Find("Customer Score %", , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then

  ColLetter = Split(Cell.Address, "$")(1)
     trcol = """ & ColLetter & """
    
     
Else
  MsgBox "I cannot find that text on this sheet"
End If
''''''''''''''''''''''
'the loop
''''''''''''''''''''''
Dim N As Long, i As Long

Workbooks.Open (ThisWorkbook.Path & workb)
N = Cells(Rows.Count, sccol).End(xlUp).Row


For i = 2 To N
Dim totalRnNum As Integer: totalRnNum = Range("F100").End(xlUp).Row
Dim totalVal As Double: totalVal = Cells(totalRnNum, 6).Value
Cells(i, **trcol**).Copy '''''''here i need to have the result of the search as trcol


Next i

 

[Fluff]

You can use

  trcol = Split(cell.Address, "$")(1)

and then

Cells(i, trcol).Copy

 

[Alex Blakenburg]

I am not sure why you are trying to work with the Column Letter when it is simpler to use the column number.

Dim trcol as Long
trcol = Cell.Column
Cells(i, trcol).Copy

 

[adinev]

trcol = Split(cell.Address, "$")(1)

Copies the wrong column unfortunately

 

[Fluff]

That will copy from the column where "Customer Score %" is found.

 

[adinev]

Hi all ,used the index instead of letter,works now.Thank you!!

 

[Fluff]

Glad you sorted it & thanks for letting us know.