Row count
 

Hi all,

How do I write the code for the following. I need a vba to look up the
value in column "a" on a sheet called "reg". If the cells value is greater
than 1 i need in column "g" to input this code .

=IF(A2 500,"yes","no")

I want this to show the row number it is on instead of "a2" it might be a31
or something

Any help would be great as I have no idea how to do this

Thanks

Greg

Row count
 

Just a follow on i need it to do it for all the rows in the worksheet.
Thanks again

greg
"Greg" wrote in message
...
Hi all,

How do I write the code for the following. I need a vba to look up the
value in column "a" on a sheet called "reg". If the cells value is
greater than 1 i need in column "g" to input this code .

=IF(A2 500,"yes","no")

I want this to show the row number it is on instead of "a2" it might be
a31 or something

Any help would be great as I have no idea how to do this

Thanks

Greg

Row count
 

Hi Greg,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourWorkbook.xls") '<<==== CHANGE
Set SH = WB.Sheets("reg")
Set rng = Intersect(SH.UsedRange, SH.Columns("A"))

For Each rCell In rng.Cells
With rCell
If IsNumeric(.Value) And .Value 1 Then
rCell(1, 7).FormulaR1C1 = _
"=IF(RC[-6] 500,""yes"",""no"")"
End If
End With
Next rCell
End Sub
'<<=============


---
Regards,
Norman



"Greg" wrote in message
...
Just a follow on i need it to do it for all the rows in the worksheet.
Thanks again

greg
"Greg" wrote in message
...
Hi all,

How do I write the code for the following. I need a vba to look up the
value in column "a" on a sheet called "reg". If the cells value is
greater than 1 i need in column "g" to input this code .

=IF(A2 500,"yes","no")

I want this to show the row number it is on instead of "a2" it might be
a31 or something

Any help would be great as I have no idea how to do this

Thanks

Greg

Row count
 

Thanks for that Norman

Greg
"Norman Jones" wrote in message
...
Hi Greg,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("YourWorkbook.xls") '<<==== CHANGE
Set SH = WB.Sheets("reg")
Set rng = Intersect(SH.UsedRange, SH.Columns("A"))

For Each rCell In rng.Cells
With rCell
If IsNumeric(.Value) And .Value 1 Then
rCell(1, 7).FormulaR1C1 = _
"=IF(RC[-6] 500,""yes"",""no"")"
End If
End With
Next rCell
End Sub
'<<=============


---
Regards,
Norman



"Greg" wrote in message
...
Just a follow on i need it to do it for all the rows in the worksheet.
Thanks again

greg
"Greg" wrote in message
...
Hi all,

How do I write the code for the following. I need a vba to look up the
value in column "a" on a sheet called "reg". If the cells value is
greater than 1 i need in column "g" to input this code .

=IF(A2 500,"yes","no")

I want this to show the row number it is on instead of "a2" it might be
a31 or something

Any help would be great as I have no idea how to do this

Thanks

Greg