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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:
Upvote 0
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!
 
Upvote 0
Just to be clear: it was my brain melting down. My first attemted reply was high grade stupidity.. ;)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,437
Messages
6,119,516
Members
448,903
Latest member
StephMJ

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