CODEWITHSUNDEEP
vbaexcelexcel-formula
Viktor Vidolov
Viktor Vidolov

Reputation: 1

Applying an Excel formula with macro

Thank you for taking the time to read my query.

I have a problem with applying a formula to one of my Excel sheets. I'm currently using a macro to combine few sheets into one. It's quite rough but it does the job.

Sub Combine()
    Application.DisplayAlerts = False
    Sheets("Combined").Delete
    Application.DisplayAlerts = True

    Dim J As Integer

    On Error Resume Next

    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    For J = 3 To 6
        Sheets(J).Activate
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
        Sheets("Combined").Visible = False
    Next
End Sub

This is giving me a specific page from which I need to draw the info. I will tie it to button for easy access in the future. I'm currently struggling with applying a formula that draws info from the aforementioned 'Combined' sheet. The formula that I'm using is lost upon deleting the sheet in the beginning of the code.

=IF(ISNUMBER(SEARCH("_",Combined!A2)),LEFT(Combined!A2,(FIND("_",Combined!A2,1)-1)))

So I tried applying it to a macro. But as you can see there is an underscore in there, that VBA has a very specific interpretation of it. Is there a workaround?

Sub Place_formula()
'trying to place the formulae once again
Range("F2").Formula = 
"=IF(ISNUMBER(SEARCH("_",Combined!A2)),LEFT(Combined!A2,
(FIND("_",Combined!A2,1)-1)))"
End Sub

If I manage to do this I will easily find a way to replicate it to where it is needed.

Upvotes: 0

Views: 45

Answers (1)

SJR
SJR

Reputation: 23081

You must double up the quotes in VBA

Range("F2").Formula = "=IF(ISNUMBER(SEARCH(""_"",Combined!A2)),LEFT(Combined!A2,(FIND(""_"",Combined!A2,1)-1)))"

Also suggest amending your main code to avoid the selecting, and using some worksheet variables to make it easier to refer to relevant sheets.

Sub Combine()

Application.DisplayAlerts = False
workSheets("Combined").Delete
Application.DisplayAlerts = True

Dim ws1 As Worksheet, ws2 As Worksheet
Dim J As Long

Set ws1 = Sheets(1)
Set ws2 = Worksheets.Add(before:=ws1)
ws2.Name = "Combined"
ws1.Range("A1").EntireRow.Copy Destination:=ws2.Range("A1")

For J = 3 To 6
    With workSheets(J).Range("A1").CurrentRegion
        .Offset(1, 0).Resize(.Rows.Count - 1).Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp)(2)
    End With
Next
ws2.visible = False

End Sub

Upvotes: 1

Related Questions