Last Used Range

[fari1]

<b>Sheet2</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:145px;" /><col style="width:172px;" /><col style="width:233px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7359</td><td >CONDENSED CONSOLIDATING STATEMENT OF INCOME </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7360</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7361</td><td > </td><td > </td><td >Nine Months Ended September 30, 2006</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7362</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7363</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7364</td><td >In millions of dollars</td><td > </td><td >Citigroup</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7365</td><td > </td><td > </td><td >parent</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7366</td><td > </td><td > </td><td >company</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

i've a line of code, which brings in a cell value after last used range in column A from another sheet with my below piece of code

wsDst.Range("AA" & Rows.Count).End(xlUp)(2) = wsSrc.Range("A1").Value

while i want to get the value in column A after the last used range from B to onwards columns, meaning in above example the value to be brought in by code in cell A7367.

hope i made my point clear, want help on it urgently

 

[wigi]

Urgently or not, that is irrelevant since you ask a favour from voluntary helpers.

Try:

wsDst.Range("AA" & Rows.Count).End(xlUp)(2) = wsSrc.Range("A1").Value

 

[fari1]

hi, thanks for the help, you posted the code that checks for the last used range and brings in data in A7367? u posted my line of code as it is, didn't change anything

 

[wigi]

Oops, error.

wsDst.Range("AA" & wsDst.UsedRange.Offset(, 1).Find(what:="*", after:=wsDst.[B1], searchdirection:=xlPrevious).Row + 1).Value = wsSrc.Range("A1").Value

 

[fari1]

it is giving type mismatch error highlighting this line of code

 

[wigi]

For me on a test file it works fine, so it must be something in your file.

Please debug using F8, MsgBox, the Immediate Window, removing sheet references, and so on.

 

[fari1]

can u send me that test file?, its not working here

 

[fari1]

this is my complete code

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)
wsDst.Range("AA" & wsDst.UsedRange.Offset(, 1).Find(what:="*", after:=wsDst.[B1], searchdirection:=xlPrevious).Row + 1).Value = wsSrc.Range("A1").Value
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

 

[wigi]

Start a new empty file, fill the appropriate cells (a.o. cell Z1), and consider:

Sub Wigi()
    
    Range("A" & ActiveSheet.UsedRange.Offset(, 1).Find(what:="*", after:=[B1], searchdirection:=xlPrevious).Row + 1).Value = Range("Z1").Value

End Sub

If has no sheet references so you can then test sheet references as a cause of your error.

 

[fari1]

its working in new sheet, with my workbook, i'm running this code in wssrc and want the output in wsdst,considering the used values in wsdst, and want the Cell A1 value from wssrc, its not working there, unable to find out

wsDst.Range("A" & wsDst.UsedRange.Offset(, 1).Find(what:="*", after:=[B1], searchdirection:=xlPrevious).Row + 1).Value = wsSrc.Range("A1").Value