looping thru rows

[Ray]

POSTED: SEARCH - LOOPING, IN EXCEL PROGRAMMING

Hello,

I am trying convert a value of one column (col 12 is value) to negative if
the value in another column (col 10 is cs qty) is negative. The result would
update to column 14 as - or + value.

The function below works but I cant get it to loop thru the entire rows. The
amount of records changes daily. I am trying to loop this using do until but
I cant get it to work. Would you please show me how to get this to loop thru
the entire rows?

Function Z_UpdateCell()

If Worksheets("ListofDiff").Cells(2, 10) < 0 Then
Worksheets("ListofDiff").Cells(2, 14) = -(Cells(2, 12))
Else: Worksheets("ListofDiff").Cells(2, 14) = (Cells(2, 12))
End If


End Function

thanks, ray

 

[Don Guillett]

Try this idea. Notice the dots .......

Function Z_UpdateCell()
With Sheets("ListofDiff")
For i = 2 To 7
If .Cells(i, 10) < 0 Then
.Cells(i, 14) = -.Cells(i, 12)
Else
.Cells(i, 14) = .Cells(i, 12)
End If
Next i
End With
End Function

 

[Ryan H]

I change your code a bit.

1.) Since you are not returning any kind of data it is not neccessary to
use a Function. So I changed it to a Sub. No big deal though.

2.) I changed your cell references from Cells(2,10) to Cells(2, "J"). I
don't like to label columns by number because it can be hard to debug.

3.) When looping thru a range of cells (or objects) it is recommended to
use the For Each...Next Loop instead of Do...Loop or For...Next Loop. But
since you wanted the Do Until that is what I gave you.

4.) Give this code a try. Hope it helps! If so, let me know, click "YES"
below.

Sub Z_UpdateCell()

Dim LastRow As Long
Dim i As Long

With Sheets("ListofDiff")
LastRow = .Cells(Rows.Count, "J").End(xlUp).Row

i = 2
Do Until i > LastRow
If .Cells(i, "J") < 0 Then
.Cells(i, "N") = -(.Cells(i, "L"))
Else
.Cells(i, "N") = .Cells(i, "L")
End If
i = i + 1
Loop
End With

End Sub

 

[Ryan H]

Here is an example of using the For Each...Next Loop instead of the Do...Loop
that I gave you in the earlier post. Hope this helps! If so, let me know,
click "YES" below.

Sub Z_UpdateCell()

Dim LastRow As Long
Dim MyRange As Range
Dim rng As Range


With Sheets("ListofDiff")

' find last row in Col. J
LastRow = .Cells(Rows.Count, "J").End(xlUp).Row

' set range to loop thru
Set MyRange = .Range("J2:J" & LastRow)

For Each rng In MyRange

' test if rng is empty, if so skip it
If Not IsEmpty(.Cells(rng.Row, "J")) Then
If .Cells(rng.Row, "J") < 0 Then
.Cells(rng.Row, "N") = -(.Cells(rng.Row, "L"))
Else
.Cells(rng.Row, "N") = .Cells(rng.Row, "L")
End If
End If
Next rng
End With

End Sub

 

[JLGWhiz]

First things first: A function can only return a value to a single cell, so
you need to use a Sub procedure.

Sub Z_UpdateCell()
Dim lr As Long, sh As Worksheet
Set sh = Worksheets("ListofDiff")
lr = sh.Cells(Rows.Count, 10).End(xlUp).Row
For i = 2 To lr
If Worksheets("ListofDiff").Cells(i, 10) < 0 Then
Worksheets("ListofDiff").Cells(i, 14) = -(Cells(i, 12))
Else: Worksheets("ListofDiff").Cells(i, 14) = (Cells(i, 12))
End If
Next

End Sub

See if this works for you. If you get errors post back.

 

[Don Guillett]

Test mine

 

[JLGWhiz]

It runs, but as a sub. It cannot be called as a function from a worksheet
cell to return a value. It returns a #Value! error on the worksheet.

 

[Don Guillett]

Since the original question was to run for more than one, I didn't change
it. I also would have normally used SUB but didn't in this case. And, it did
as OP desired.

 

[Ray]

Hi Don,
This works if the row count is known. in this case 7; I got my problem
solved with help from Ryan's code. Thanks very much for your help.
Ray

 

[Ray]

Hi Ryan,
Thanks for your help. Sorry for taking so long to check this but I could not
find my post until now. Both of your codes worked well. many thanks, Ray

 

[Don Guillett]

lr=.cells(rows.count,whichcolum).end(xlup).row
for i=1 to lr

 

[Ray]

Hello,

This works perfect. Sorry for taking so long. I could not find my post
question. Also, thanks for pointing out "first things first" in regards to
using functions instead of sub. I will remember that. Thanks again, Ray