Help with VBA coding

yitm03

New Member
Joined
Mar 14, 2018
Messages
4
[h=2]Hi there, Stuck with an issue for over a week and need someone to point me in the right direction. I recorded a macro to do a advanced filter and copy the data to another sheet. Works great with one drawback! I have over 3000 rows and created Do While Loop to get the values into 2 different columns but the macro had a fixed range from doing it manually and obviously it just creates 3000 sheets with the same value.
Sub calcModO()
Dim row As Integer
row = 2

Do While Cells(row, 6) <> ""
Cells(row, 7).Value = Cells(row, 9).Value * 0.85
Cells(row, 8).Value = Cells(row, 9).Value * 1.15

Sheets.Add After:=ActiveSheet
Range("A1").Select

Sheets("sheet1").Range("A1:AF1262").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("DIA (4)").Range("G2:H2"), CopyToRange:=Range( _
"A1"), Unique:=False

row = row + 1
Loop

End Sub

How can I, in the loop have the CriteriaRange:=Sheets("sheet1").Range("G2:H2"), change to the next row ("G3:H3")? Do I need to call on a procedure or can it be done within the code above?
Thanks for any much needed hep.[/h]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
change the criteria line to something like this: ( not Tested!!)
Code:
CriteriaRange:=Sheets("DIA (4)").Range("G" & Row & ":H & Row ), CopyToRange:=Range( _
 
Upvote 0
change the criteria line to something like this: ( not Tested!!)
Code:
CriteriaRange:=Sheets("DIA (4)").Range("G" & Row & ":H & Row ), CopyToRange:=Range( _
.

Thanks for the reply. I made the change but now getting a compile error with that change. Thanks all the same, much appreciated.
 
Upvote 0
My fault I don't usually use that addressing mode I write it like this which use columns numbers which I find easier because it is easier to use indices: try this:
Code:
CriteriaRange:=Sheets("DIA (4)").Range(Cells(Row, 7), Cells(Row, 8)), CopyToRange:=Range( _

I have managed to crack the compile error by doing the following:

Code:
Dim txt As String
txt = "G" & Row & ":H" & Row


Sheets("DIA (4)").Range(txt), CopyToRange:=Range( _
[/CODE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top