Prev: Excel 2007 query - Access MDB question
Next: How to: obtain the filename that a module exists in, via VBA?
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" <Ray(a)discussions.microsoft.com> 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" <Ray(a)discussions.microsoft.com> 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
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Excel 2007 query - Access MDB question
Next: How to: obtain the filename that a module exists in, via VBA?