Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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 -- Cheers, Ryan "Ray" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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 -- Cheers, Ryan "Ray" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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. "Ray" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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" wrote in message ... Test mine -- Don Guillett Microsoft MVP Excel SalesAid Software "JLGWhiz" wrote in message ... 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. "Ray" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "JLGWhiz" wrote in message ... 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" wrote in message ... Test mine -- Don Guillett Microsoft MVP Excel SalesAid Software "JLGWhiz" wrote in message ... 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. "Ray" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Ray" wrote in message ... 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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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 "Ryan H" wrote: 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 -- Cheers, Ryan "Ray" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
lr=.cells(rows.count,whichcolum).end(xlup).row for i=1 to lr -- Don Guillett Microsoft MVP Excel SalesAid Software "Ray" wrote in message ... 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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Ray" wrote in message ... 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 . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping thru rows
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 "JLGWhiz" wrote: 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. "Ray" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping to through rows | Excel Programming | |||
looping through columns and rows | Excel Programming | |||
looping through rows and columns | Excel Discussion (Misc queries) | |||
Not looping through rows | Excel Programming | |||
Looping through Columns then Rows | Excel Programming |