Where to put code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joanne - 31 Dec 2007 01:26 GMT

Using MSOffice2003
On my spreadsheet, I want to do this:

If C15.value > 0 then D15.value = 65
Then I want to copy this down these two rows.

Where do I put this code, and am I using proper Excel VBA syntax?

Thanks for your help.

Joanne

Reply to this Message

Gary''s Student - 31 Dec 2007 02:11 GMT

Sub joanne()
For i = 15 To Cells(Rows.Count, "C").End(xlUp).Row
If Cells(i, "C").Value > 0 Then
Cells(i, "D").Value = 65
End If
Next
End Sub

Put this in a standard module

Signature

Gary''s Student - gsnu20076b

> Using MSOffice2003
> On my spreadsheet, I want to do this:
[quoted text clipped - 7 lines]
>
> Joanne

Reply to this Message

Joanne - 31 Dec 2007 13:26 GMT

I copied your code into a standard module in my workbook as you
instructed, but it does not do the intended deed.

As I understand the code, it is creating an index of all cells in col
'c' starting with row 15 to the end of the row - then looking (row by
row) to see if the cell in c has a value greater than 0 - if so, then
the cell in that row in col 'd' should be equal to 65. Is that right?

It seems a simple thing. I don't understand why it doesn't work. I have
the cells in question formatted for currency with a leading $ - would
that make any difference?

What I am trying to do on the spreadsheet is make col D = 65 only in the
rows where there will be a value > 0 in col C - maybe I am taking the
wrong approach altogether?

Thanks for your interest in my problem
Joanne

> Sub joanne()
> For i = 15 To Cells(Rows.Count, "C").End(xlUp).Row
[quoted text clipped - 5 lines]
>
> Put this in a standard module

Reply to this Message

John Bundy - 31 Dec 2007 16:13 GMT

You probably just need to be sheet specific since it is in a module.
Sub joanne()
For i = 15 To Sheets(1).Cells(Rows.Count, "C").End(xlUp).Row
If Sheets(1).Cells(i, "C").Value > 0 Then
Sheets(1).Cells(i, "D").Value = 65
End If
Next
End Sub

change the 1 to your sheet number or name.

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> I copied your code into a standard module in my workbook as you
> instructed, but it does not do the intended deed.
[quoted text clipped - 24 lines]
> >
> > Put this in a standard module

Reply to this Message

Joanne - 31 Dec 2007 19:38 GMT

remove wrote:
That did not help any. Still no value dropped in row on col D. Should I
be using the column headers for cols C and D instead of just 'C' and
'D'?

The code is in a module, not on the ws or in the wb. This is correct?
Sub is named LaborRate, which I believe should be okay.

Thanks for your time on this
Joanne

> You probably just need to be sheet specific since it is in a module.
> Sub joanne()
[quoted text clipped - 6 lines]
>
> change the 1 to your sheet number or name.

Reply to this Message

John Bundy - 31 Dec 2007 20:16 GMT

The following is exactly what i used, I tried it with a $ in front, storing
as text and anything else i could think of and it worked fine. I wonder, do
you know you have to run this everytime you want 65 to appear? it does not do
this automatically, if you need that let me know. If this code does not work
for you then feel free to send me the sheet and I will see what's going on
first hand. jmbundy@gmail.com.removethis

Sub joanne()
For i = 15 To Sheets(1).Cells(Rows.Count, "C").End(xlUp).Row
If Sheets(1).Cells(i, "C").Value > 0 Then
Sheets(1).Cells(i, "D").Value = 65
End If
Next
End Sub

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> remove wrote:
> That did not help any. Still no value dropped in row on col D. Should I
[quoted text clipped - 17 lines]
> >
> > change the 1 to your sheet number or name.

Reply to this Message