VBA to look for next non blank cell

[Simondo]

Hi All,

I have a VBA code to look in column G to find the first non blank cell and return the value in the same row on column C and copy and paste the result to another workbook

Here is the code which works ok

'Now, copy what you want from InputFile:
InputFile.Sheets("Customer Timesheet").Activate
InputFile.Sheets("Customer Timesheet").Range("G16").End(xlDown).Offset(0, -4).Copy


'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

However if there is a Value already in the first cell "G16" the code doesn't return the value i want which should be "C16", It seems to continue until it finds the next blank cell.

I have tried playing around with the code but to no avail, please see below the code i have been trying but this code returns a blank.

'Now, copy what you want from InputFile:
InputFile.Sheets("Customer Timesheet").Activate
If Range("G16") <> "" Then
Range("G16").Offset(0, -4).Copy
Else
InputFile.Sheets("Customer Timesheet").Range("G16").End(xlDown).Offset(0, -4).Copy


'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If

I'm new enough to VBA so not sure where i am going wrong, any help is greatly appreciated.

Regards,
Simon

 

[JLGWhiz]

The End function in VBA works exactly like the End button on the keyboard for directional maneuvering. If you use End(xlDown) and the starting point is on a blank cell, it locates the next cell with data moving downward. If the starting point is a cell with data or a formula, it locates the last cell in the series of cells that have data or formulas. It might be better to use a different criteria to locate your cell if your data and formula layout is inconsistent.