From: Ray on 25 Jan 2010 17:42 Hi There, I have been trying to populate a column (column 14) in Excel using VB for a while and I just can't seem to solve my problem. I am looking at 3 colums; columns 10,12,&14. colum 10 is qty, colum 12 is Absolute dollar value. if colum 10 is not null and less than 0, then colum 14= (- colum 12) or else column 14 = (+column 12). I am able to do the first cell but cannot loop thru the entire column. the number of records changes from time to time. I tried the looping method but I can't get it right. Below is what I have that works but I can't do the looping. I would grealty appreciate your help. Sub 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 Sub Thanks, Ray From: JLGWhiz on 25 Jan 2010 19:26 Sub Z_UpdateCell() Dim lr As Long Dim sh As Worksheet Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 10).End(xlUp).Row For i = 2 To lr '<===Assumes header row 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 "Ray" wrote in message news:75D13DBB-4A73-442F-8360-821F8FBB92C0(a)microsoft.com...> Hi There, > I have been trying to populate a column (column 14) in Excel using VB for > a > while and I just can't seem to solve my problem. > > I am looking at 3 colums; columns 10,12,&14. colum 10 is qty, colum 12 is > Absolute dollar value. if colum 10 is not null and less than 0, then colum > 14= (- colum 12) or else column 14 = (+column 12). > > I am able to do the first cell but cannot loop thru the entire column. the > number of records changes from time to time. I tried the looping method > but I > can't get it right. > > Below is what I have that works but I can't do the looping. I would > grealty > appreciate your help. > > Sub 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 Sub > > Thanks, Ray From: Barb Reinhardt on 25 Jan 2010 19:37 Try something like this Dim myRow as long Dim lRow as long Dim myWS as excel.worksheet set myWS = WOrksheets("ListofDiff") lRow = myws.cells(myws.rows.count,10).end(xlup).row for myrow = 2 to lrow if myws.cells(i,10) < 0 then myws.cells(myrow,14) = -Cells(myrow,12) else myws.cells(myrow,14) = cells(myrow,12) end if next myrow next myrow -- HTH, Barb Reinhardt "Ray" wrote: > Hi There, > I have been trying to populate a column (column 14) in Excel using VB for a > while and I just can't seem to solve my problem. > > I am looking at 3 colums; columns 10,12,&14. colum 10 is qty, colum 12 is > Absolute dollar value. if colum 10 is not null and less than 0, then colum > 14= (- colum 12) or else column 14 = (+column 12). > > I am able to do the first cell but cannot loop thru the entire column. the > number of records changes from time to time. I tried the looping method but I > can't get it right. > > Below is what I have that works but I can't do the looping. I would grealty > appreciate your help. > > Sub 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 Sub > > Thanks, Ray From: J_Knowles on 25 Jan 2010 20:36 Sub Z_UpdateCell() Dim lastrow As Long Dim i As Long Set sh = ActiveSheet Set ws = Worksheets("ListofDiff") lastrow = sh.Range("J" & Rows.Count).End(xlUp).Row For i = 2 To lastrow ws.Cells(i, 14).Value = Sgn(ws.Cells(i, 10)) * ws.Cells(i, 12) Next i End Sub HTH, -- Data Hog "Ray" wrote: > Hi There, > I have been trying to populate a column (column 14) in Excel using VB for a > while and I just can't seem to solve my problem. > > I am looking at 3 colums; columns 10,12,&14. colum 10 is qty, colum 12 is > Absolute dollar value. if colum 10 is not null and less than 0, then colum > 14= (- colum 12) or else column 14 = (+column 12). > > I am able to do the first cell but cannot loop thru the entire column. the > number of records changes from time to time. I tried the looping method but I > can't get it right. > > Below is what I have that works but I can't do the looping. I would grealty > appreciate your help. > > Sub 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 Sub > > Thanks, Ray From: Ray on 2 Feb 2010 12:22 Thanks very much, this works! Sorry for taking so long to respond. I could not find my posting. Ray "JLGWhiz" wrote: > Sub Z_UpdateCell() > Dim lr As Long > Dim sh As Worksheet > Set sh = ActiveSheet > lr = sh.Cells(Rows.Count, 10).End(xlUp).Row > For i = 2 To lr '<===Assumes header row > 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 > > > > > "Ray" wrote in message > news:75D13DBB-4A73-442F-8360-821F8FBB92C0(a)microsoft.com... > > Hi There, > > I have been trying to populate a column (column 14) in Excel using VB for > > a > > while and I just can't seem to solve my problem. > > > > I am looking at 3 colums; columns 10,12,&14. colum 10 is qty, colum 12 is > > Absolute dollar value. if colum 10 is not null and less than 0, then colum > > 14= (- colum 12) or else column 14 = (+column 12). > > > > I am able to do the first cell but cannot loop thru the entire column. the > > number of records changes from time to time. I tried the looping method > > but I > > can't get it right. > > > > Below is what I have that works but I can't do the looping. I would > > grealty > > appreciate your help. > > > > Sub 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 Sub > > > > Thanks, Ray > > > . >