VBa to check last used range in any column from A to Z and give output in column AA in corresponding cell

[fari1]

hi, i've a code which brings my the below output in sheet3, the code is

Sub findTEXT()
 
    Static wsSrc As Worksheet: Set wsSrc = ActiveWorkbook.Sheets("Sheet2")
    Static wsDst As Worksheet: Set wsDst = ActiveWorkbook.Sheets("Sheet3")
 
    Static StartAddress As String
    Dim rngFnd As Range
    Dim rngAll As Range
    Dim allfound As Boolean
 
    Set rngFnd = wsSrc.UsedRange.Find("consolidated")
    If Not rngFnd Is Nothing Then StartAddress = rngFnd.Address
 
    While Not rngFnd Is Nothing And allfound = False
        If RowLen(wsSrc, rngFnd) < 50 And rngAll Is Nothing Then Set rngAll = rngFnd
        Set rngFnd = wsSrc.UsedRange.Find("consolidated", rngFnd)
        If RowLen(wsSrc, rngFnd) < 50 Then
            If rngAll Is Nothing Then Set rngAll = rngFnd
            If Intersect(rngAll, rngFnd) Is Nothing _
            And Intersect(rngAll.EntireRow, rngFnd) Is Nothing _
            Then Set rngAll = Union(rngAll, rngFnd)
        End If
        If rngFnd.Address = StartAddress Then allfound = True
    Wend
    If Not rngAll Is Nothing Then rngAll.EntireRow.Copy wsDst.Cells(Rows.Count, "A").End(xlUp).Offset(1)
 
End Sub
Function RowLen(ws As Worksheet, rng As Range) As Long
 
    Dim c As Range
    For Each c In Intersect(rng.EntireRow, ws.UsedRange)
        RowLen = RowLen + Len(c.Text)
    Next c
 
End Function

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >CONSOLIDATED FINANCIAL STATEMENTS </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >CONSOLIDATED STATEMENT OF INCOME (Unaudited) </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >CONSOLIDATED BALANCE SHEET </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >CONSOLIDATED STATEMENT OF CASH FLOWS (UNAUDITED) </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >CONSOLIDATED BALANCE SHEET </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Total assets of consolidated VIEs</td><td > </td><td >$</td><td style="text-align:right; ">46.6</td><td > </td><td >$</td><td style="text-align:right; ">47.5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Citigroup</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >subsidiaries,</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Citigroup</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Citigroup</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Citigroup</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Citigroup</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Citigroup</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td >parent</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Citigroup</td><td > </td><td > </td><td >adjustments</td><td > </td><td > </td><td >Consolidated</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

after every run the code brings me in the above output,as i'm running the loop, so i want that every time the code runs, it check for the last used cell in any column from A to B and in column AA give me the text string by my below code

wsdst.Range( & Rows.Count).End(xlUp)(2) = wssrc.Range("A1").Value

with my above sample sheet, my above code would bring in the text string in AA16, as the last used cell is X15.

 

[fari1]

range A1 contains the text string that i want to copy in sheet3 after every time the code runs

 

[fari1]

anybody