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

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

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

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

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


.

No problem, glad I could help.


"Ray" wrote in message
...
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
...
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


.