Replace Range formula in VBA

elman

New Member
Joined
Feb 25, 2016
Messages
9
Hello everybody!


I m new in vba so apologies if the question is easy or is already answered.


I want to:
-copy data from a sheet
-paste in another sheet (different .xlsx)
-copy some formulas from another sheet to the above sheet to make the calculations


For this i want to replace "=" with "#" to move formulas to the sheet


The question is: When i run the macro i get an error at ---> str1 = Range("EH2:FU2")


When i am deleting the rows with the strings, ranges etc in module the macro runs fine.


Below is the macro.


Thank you in advance.


Br
Elma


--------------------------------------------------------------------------
Sub copypaste()


Dim str1 As String
Dim str2 As String


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


'copy-paste data from query


Sheets("DATA_A").Select
Range(Cells(2, 1), Cells(50000, 137)).Clear


Windows("Query.xlsx").activate
Sheets("DATA").Select
Range(Cells(2, 1), Cells(50000, 137)).Copy
Windows("EOC07_16.xlsm").activate
Sheets("DATA_A").Select
Cells(2, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


'copy formulas from admin sheet


Sheets("DATA_A").Select
Range("EH2:FU50000").Clear
Sheets("Admin").Select
str1 = Range("EH2:FU2")
srt1 = Replace(str1, "=", "#")
Range("EH2:FU2").Copy
str2 = Range("EH2:FU50000")
srt2 = Replace(str2, "=", "#")
Sheets("DATA_A").Select
Range("EH2:FU50000").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.Copy
Range("EH2:FU50000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Application.Calculation = xlCalculationAutomatic


End Sub
--------------------------------------------------------------------------
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
brain meltdown. :(

second attempt:

wouldn't it be easier to have something like instead of worrying about the '=' and '#'?
Range("EH2:FU50000").formula = "VBA version of your formula goes here. Record it for correct syntax"
 
Last edited:

elman

New Member
Joined
Feb 25, 2016
Messages
9
brain meltdown. :(

second attempt:

wouldn't it be easier to have something like instead of worrying about the '=' and '#'?
Range("EH2:FU50000").formula = "VBA version of your formula goes here. Record it for correct syntax"



hehe I guess u r wright its a meltdown :p

I'll try it! Thank you very much!
 

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
Just to be clear: it was my brain melting down. My first attemted reply was high grade stupidity.. ;)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,399
Messages
5,601,452
Members
414,450
Latest member
Cassy_sn

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
Top