PDA

View Full Version : Return the value to the right of the cell



Romulo Avila
09-02-2016, 08:14 AM
Good afternoon,
I found it very interesting this VBA code and would like a help instead of seeking a particular cell that he seeks the word "SALE TOTAL" and return the value of the first cell right.

jolivanes
09-02-2016, 09:16 AM
One should not hijack someone else's thread. Start your own and refer to this thread if it is relevant.


See if this works (I did not test it)
Change this line

ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Workbooks(wb).Sheets(i).Range("B32").Value
to this

ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Workbooks(wb).Sheets(i).UsedRange.Find("SALE TOTAL", , , 1).Offset(, 1).Value

Romulo Avila
09-02-2016, 12:53 PM
Jolivanes,

Thanks, did not know this condition, the next time I will open a new thread.

jolivanes
09-02-2016, 02:29 PM
No problem. Did the code work for you?

Romulo Avila
09-05-2016, 11:22 AM
Jolivanes,


Good afternoon,


You change as you passed and is returning the message.
17022

jolivanes
09-05-2016, 11:59 AM
Can you attach a clean (no personal info) version of your workbook.
Is your Office an English version?

Romulo Avila
09-05-2016, 01:11 PM
Good afternoon,

SamT
09-05-2016, 02:14 PM
Sub Copy_From_All_Sheets_In_All_Workbooks()
Dim wb As String, i As Long
Dim t
Dim Found As Range
Dim Dest As Range

Application.ScreenUpdating = False
t = Timer

Set Dest = ThisWorkbook.Sheets("Plan1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
wb = Dir(ThisWorkbook.Path & "\*.xls*")
Do Until wb = ""
If wb <> ThisWorkbook.Name Then
Workbooks.Open ThisWorkbook.Path & "\" & wb

With Workbooks(wb)
For i = 1 To Workbooks(wb).Sheets.Count
Set Found = .Sheets(i).UsedRange.Find("SALE TOTAL")
If Not Found Is Nothing Then
Dest.Value = Found.Offset(, 1).Value
Set Dest = Dest.Offset(1)
End If
Next i
.Close False
End With
End If
wb = Dir
Loop

Application.ScreenUpdating = True
MsgBox "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to copy" & vbLf & _
"data from all sheets in all closed workbooks."
End Sub

"Find().Offset" was the problem when item not found