Not sure if I need a formula or some code....

C

Candee

Hello all.

I have a worksheet with 70 columns of data. I need to look bac
through all columns in each row (I will need the formula in each cel
of the last row), find the first blank cell, and copy the data from th
cell in the same row but 2 columns before the blank cell.

eg. Cell T34 is blank, I want the value (text) from cell R34 copie
into cell CD 34.

I hope I've explained enough.

Thanks in advance..
 
K

kkknie

Here's a code solution:

Code
-------------------
Sub test()

Dim iFirstRow As Long
Dim iLastRow As Long
Dim iFirstCol As Integer
Dim iLastCol As Integer

Dim i As Long
Dim j As Long

iFirstRow = 1 'Change to your first data row
iLastRow = Range("A65536").End(xlUp).Row
iFirstCol = 1 'Change to your first data column
iLastCol = 70 'Change to your last data column

For i = iFirstRow To iLastRow
For j = iFirstCol To iLastCol
If Cells(i, j).Value = "" Then
Cells(i, 82).Value = Cells(i, j - 2)
Exit For
End If
Next
Next

End Su
 
F

Frank Kabel

Hi
enter the following array formula (entered with CTRL+SHIFT+ENTER) in
CD34
=INDEX(A34:CC34,1,MIN(IF(A34:CC34="",COLUMN(A34:CC34)))-2)
 
B

Bernie Deitrick

Candee,

In CD34, Array enter, using Ctrl-Shift-Enter

=INDEX(34:34,1,MIN(IF(A34:CC34="",COLUMN(A34:CC34),256))-2)

Copy up and down the column as needed.

HTH,
Bernie
MS Excel MVP
 
C

Candee

Thank you both for your quick replies. I couldn't get the code to work
but the formula works like a charm - just what I was after.

Thank you again :
 
Top