VBA if loop
 

Hi!

I'm working as accountant and I need a little help with a macro that
could relief me from several hours of scanning and replace

Basically I got to work with several sheets in excel 2000 where in
each row I have to change a value of a cell in another one (the value
that is in that cell divided by 2) if the another cell in that row is
equal to a certain value in another worksheet. Explained:

Sheet1
62 colums
Row 1
if the value in the column 51 is equal to value in Sheet2!A1 then
replace the number in column 44 with that number divided by 2. Go to
the next row till end.

Many thanks for your time and your help!

Tony

VBA if loop
 

Hi,

Try this.
Right click the sheet tab, view code and paste this in

Sub stance()
lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)
For Each C In Myrange
If C.Value = Worksheets("Sheet2").Range("A1").Value Then
C.Offset(0, -7).Value = C.Offset(0, -7).Value / 2
End If
Next
End Sub


Mike

"Tony from Work" wrote:

Hi!

I'm working as accountant and I need a little help with a macro that
could relief me from several hours of scanning and replace

Basically I got to work with several sheets in excel 2000 where in
each row I have to change a value of a cell in another one (the value
that is in that cell divided by 2) if the another cell in that row is
equal to a certain value in another worksheet. Explained:

Sheet1
62 colums
Row 1
if the value in the column 51 is equal to value in Sheet2!A1 then
replace the number in column 44 with that number divided by 2. Go to
the next row till end.

Many thanks for your time and your help!

Tony

VBA if loop
 

Hi Mike!

It's working great, and you are my saver, at last the saver of my eyes
and my brain!

If I may ask also some explanation of the the code, I'm in a beginner
in VBA I' d like to learn bit more

first, I don't need to declare any variable?

lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)

Here you are "dimensioning" the matrix the will be used, right?

For Each C In Myrange
If C.Value = Worksheets("Sheet2").Range("A1").Value Then
C.Offset(0, -7).Value = C.Offset(0, -7).Value / 2
End If


here you are using the Offset to have a "positional" reference of the
value that has to be changed right?..... But, how C.Value works? it
keeps in memory the value and then divides it by two? Could I use a
modified version of this syntax to change that value with another one
I decide?

Many thanks again for the help, again, it means really a lot of time
saved for me, and sorry for abusing again your time

Thanks, thank,s thanks, and kind regards,

Tony

VBA if loop
 

Tony,

Your welcome and thanks for the feedback.
How does it work you ask.
Firstly it would be good practice to open with
'Option Explicit'
which forces variable declaration and has the added advantage of catching
typos when you think you've typed 'Myvariable' and you actually typed
'Myvaraible' and then spend a lot of time wondering why the code doesn't do
what you want.

lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)
This finds the bottom used cell in column AY and then sets a range from AY1
to that cell

For Each Cell In Myrange
If Cell.Value = Worksheets("Sheet2").Range("A1").Value Then
Cell.Offset(0, -7).Value = Cell.Offset(0, -7).Value / 2
End If
Next

The code above which I've changed slightly to make it more understandable
reads the value of every cell in the created range, compares it to sheet 2 A1
and if its the same diivides a cell 7 columns to the left by 2. Once again my
lazy code would crash here if it tried to divide text by 2 so it would have
been good practice to include the line

on error resume next

at the start of the code or check in some other way that its a number.

HTH

Mike

"Tony from Work" wrote:

Hi Mike!

It's working great, and you are my saver, at last the saver of my eyes
and my brain!

If I may ask also some explanation of the the code, I'm in a beginner
in VBA I' d like to learn bit more

first, I don't need to declare any variable?

lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)

Here you are "dimensioning" the matrix the will be used, right?

For Each C In Myrange
If C.Value = Worksheets("Sheet2").Range("A1").Value Then
C.Offset(0, -7).Value = C.Offset(0, -7).Value / 2
End If


here you are using the Offset to have a "positional" reference of the
value that has to be changed right?..... But, how C.Value works? it
keeps in memory the value and then divides it by two? Could I use a
modified version of this syntax to change that value with another one
I decide?

Many thanks again for the help, again, it means really a lot of time
saved for me, and sorry for abusing again your time

Thanks, thank,s thanks, and kind regards,

Tony

VBA if loop
 

Mike,

Your help is again priceless to me. Following your advices I've
modified the code as follow, now I could also modify other cells with
the values I need. There is no need of an error control, those colums
are a fixed output database and they are always number... Forgive my
"baboon" coding but it's working :)

....What is now beyond my grasp is to add a conditional cell formatting
(color, bold whatever) of the changed cell to have a quick check
against with the original output.

Any other advice will be more than welcomed and appreciated....

Many thanks,

Tony



Option Explicit

Dim lastrow As Variant
Dim Myrange As Variant
Dim Cell As Variant
Dim Val1 As Long


Sub stance1()

Val1 = Sheet2.Range("B1").Value

On Error Resume Next

lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)
For Each Cell In Myrange
If Cell.Value = Worksheets("Sheet2").Range("A1").Value Then
Cell.Offset(0, -7).Value = Cell.Offset(0, -7).Value / 2

End If

If Cell.Value = Worksheets("Sheet2").Range("A1").Value Then
Cell.Offset(0, -34).Value = Val1
End If

Next

End Sub

VBA if loop
 

Update!

thanks to a quiet Friday afternoon I tried to work out myself a
solution, and I came with

Option Explicit

Dim lastrow As Variant
Dim Myrange As Variant
Dim Cell As Variant
Dim Val1 As Long


Sub stance1()

Val1 = Sheet2.Range("B1").Value

On Error Resume Next

lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)
For Each Cell In Myrange

If Cell.Value = Worksheets("Sheet2").Range("A1").Value Then
Cell.Select

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.Font.Bold = True
End With

Cell.Offset(0, -7).Value = Cell.Offset(0, -7).Value / 2
Cell.Offset(0, -7).Font.Bold = True

Cell.Offset(0, -34).Value = Val1
Cell.Offset(0, -34).Font.Bold = True


End If

Next

End Sub

It looks awful but it works..... Any hint on a "slimmer" syntax ?

VBA if loop
 

Good Morning Mike and Tony,

I am trying to accomplish something similar to Tony and am
unfortunately just learning Excel VBA.

What I have a is a rawdata sheet (sheet1) with hundreds of lines of
data. What I'd like to do is based on a specific "securityname" from
sheet1 populate sheet2 with the rows specific to that "securityname"
until that securityname is no longer found on sheet1.

Is this possible with the code snippet you provided Tony this
morning? Any assistance offered is greatly appreciated. Thank you
both in advance.


On Nov 23, 10:31 am, Tony from Work wrote:
Update!

thanks to a quiet Friday afternoon I tried to work out myself a
solution, and I came with

Option Explicit

Dim lastrow As Variant
Dim Myrange As Variant
Dim Cell As Variant
Dim Val1 As Long

Sub stance1()

Val1 = Sheet2.Range("B1").Value

On Error Resume Next

lastrow = Range("AY65536").End(xlUp).Row
Set Myrange = Range("AY1:AY" & lastrow)
For Each Cell In Myrange

If Cell.Value = Worksheets("Sheet2").Range("A1").Value Then
Cell.Select

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.Font.Bold = True
End With

Cell.Offset(0, -7).Value = Cell.Offset(0, -7).Value / 2
Cell.Offset(0, -7).Font.Bold = True

Cell.Offset(0, -34).Value = Val1
Cell.Offset(0, -34).Font.Bold = True

End If

Next

End Sub

It looks awful but it works..... Any hint on a "slimmer" syntax ?