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..

--
Message posted from http://www.ExcelForum.com

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
-------------------



--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


Hello all.

I have a worksheet with 70 columns of data. I need to look back
through all columns in each row (I will need the formula in each cell
of the last row), find the first blank cell, and copy the data from
the 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 copied
into cell CD 34.

I hope I've explained enough.

Thanks in advance...


---
Message posted from http://www.ExcelForum.com/

Thank you, I'll try it

--
Message posted from http://www.ExcelForum.com

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

"Candee " wrote in message
...
Hello all.

I have a worksheet with 70 columns of data. I need to look back
through all columns in each row (I will need the formula in each cell
of the last row), find the first blank cell, and copy the data from the
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 copied
into cell CD 34.

I hope I've explained enough.

Thanks in advance...


---
Message posted from http://www.ExcelForum.com/

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 :

--
Message posted from http://www.ExcelForum.com