PDA

View Full Version : Loop Help to find Intersection Cell Address



YellowLabPro
05-22-2007, 03:35 AM
I could use some assistance in locating the first column, first row w/ a currency value.
My current loop is hardcoded w/ the column, 26. I want to run this loop on all sheets in the workbook, and the currency value is in different columns on most of them. So I need to find the first column and the first row w/ a currency value greater than zero.


For i = 1 To Cells(Rows.Count, 26).End(xlUp).Row
If Val(Cells(i, 26)) > 0 Then
'Cells(i, 26).Copy
'Wst.Cells(i - 10, 2).PasteSpecial Paste:=xlPasteValues
'Wst.Cells(i - 10, 2).Value = Wss.Cells(i, 26).Value
Wst.Cells(i - 10, 2).Value = Cells(i, 26).Value
If Val(Cells(i, 26)) > 0 Then
Cells(i, 1).Copy
Wst.Cells(i - 10, 1).PasteSpecial Paste:=xlPasteValues
End If
End If
Next i


Thanks,
Doug

Bob Phillips
05-22-2007, 03:45 AM
Something like



For Each cell in Activesheet.UsedRange
If cell.Value > 0 Then
cells.Copy
Wst.Cells(cell.row - 10, 1).PasteSpecial Paste:=xlPasteValues
End If
Next cell

YellowLabPro
05-22-2007, 04:59 AM
Hi Bob,
An issue may exist w/ this I believe is that there are other numerical values on the sheet other than currency values. So I was thinking if we can locate the first row or column w/ a currency value then we can pass this as a variable to my loop to establish the column, replace the hardcoded value of 26, in this case, and perform the copy/paste from here. Does this seem logical/possible?

thanks,

Doug

Bob Phillips
05-22-2007, 05:12 AM
How do we differentiate a currency value from any other number?

YellowLabPro
05-22-2007, 05:18 AM
I have not dug too deeply, but either w/ a number format using a currency sign, or #.00 is my thought.

YellowLabPro
05-27-2007, 08:48 AM
Is the correct format to find currency?
With Application.FindFormat.NumberFormat = "$#,##0.00_);($#,##0.00)"

If it is, how do I extract the column it finds it in?