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
--------------------------------------------------------------------------
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,851
Messages
5,833,974
Members
430,249
Latest member
Muka

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