Difficulty converting formulas inserted by VBA to Values

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hello, I am using the code below to populate my worksheet with required information. Unfortunately, I have a few fields that I've populated with formulas and I need these to be values. Is there an easy way to do this with my code below? I've tried adding ".Values" in a few places but it doesn't seem to like that (maybe I'm not using this technique properly though?)

Any thoughts and guidance would be appreciated!


Code:
Sub Module1()
    Dim wb As ThisWorkbook
    Dim Sh As Worksheet
    Dim CopyRng As Range
    
    Dim Pricing As Worksheet
    Dim BaseDate As Range
    Dim BaseDate_Full As Range
    Dim Heading_Month_1 As Range
    Dim Num_Months As Integer
    
    Dim Dest_Sh As Worksheet
    Dim Dest_Start_Row As Integer
    Dim Dest_End_Row As Integer
    Dim Dest_Start_Row_2 As Integer
    Dim Dest_End_Row_2 As Integer
    
    Dim Source_Sh As Worksheet
    Dim Source_Start_Row As Integer
    Dim Source_End_Row As Integer
        '(1.) Clear contents from destination worksheet
        Set Dest_Sh = Sheets("Destination")
        
            On Error Resume Next
                Dest_Sh.Visible = True
                Dest_Sh.Activate
            On Error GoTo 0
            
            Dest_End_Row = Dest_Sh.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
            Dest_End_Column = Dest_Sh.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
            
                If Dest_End_Row > 1 Then
                    Dest_Sh.Rows("2:" & Dest_End_Row).EntireRow.Delete
                End If
                
                If Dest_End_Column > 2 Then
                    Dest_Sh.Range(Cells(1, 3), Cells(1, Dest_End_Column)).EntireColumn.Delete
                End If
                
        '(2.) Copy source data and paste to destination worksheet
        Set Source_Sh = Sheets("Source")
            Source_End_Row = Source_Sh.Range("D" & Rows.Count).End(xlUp).Row
        
            On Error Resume Next
                Source_Sh.Visible = True
                Source_Sh.Activate
            On Error GoTo 0
            
                'FIELD 1
                Dest_Start_Row = Dest_Sh.Range("A" & Rows.Count).End(xlUp).Row + 1
                Set CopyRng = Source_Sh.Range("B10", "B" & Source_End_Row)
                        CopyRng.Copy
                        With Dest_Sh.Range("A" & Dest_Start_Row)
                            .PasteSpecial 8    ' Column width
                            .PasteSpecial xlPasteValues
                            Application.CutCopyMode = False
                        End With
                        
                'FIELD 2
                Dest_End_Row_2 = Dest_Sh.Cells(Rows.Count, "A").End(xlUp).Row
                Set Pricing = Sheets("Pricing")
                    Pricing.Visible = True
                        Set BaseDate = Pricing.Range("$I$16")
                                BaseDate.Copy
                                With Dest_Sh.Range("B" & Dest_Start_Row, "B" & Dest_End_Row_2)
                                    .PasteSpecial xlPasteValues
                                    Application.CutCopyMode = False
                                End With
                                
                        Set BaseDate_Full = Pricing.Range("$I$14")
                                BaseDate_Full.Copy
                                With Dest_Sh.Range("$C$1")
                                    .PasteSpecial xlPasteValues
                                    Application.CutCopyMode = False
                                End With
                                
                'COLUMN HEADINGS (MONTHS)
                On Error Resume Next
                    Dest_Sh.Visible = True
                    Dest_Sh.Activate
                On Error GoTo 0
                
                Set Heading_Month_1 = Dest_Sh.Range("$C$1")
                Num_Months = Pricing.Range("$I$19")
                    If Heading_Month_1 > 0 Then
[B][I][U]                        Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months)).Formula = "=DATE(YEAR(C$1),MONTH(C$1)+1,DAY(C$1))"[/U][/I][/B]
                    End If
                'MONTHLY VALUES
                On Error Resume Next
                    Dest_Sh.Visible = True
                    Dest_Sh.Activate
                On Error GoTo 0
                
                    If Heading_Month_1 > 0 Then
[U][I][B]                        Dest_Sh.Range(Cells(2, 3), Cells(Dest_End_Row_2, 2 + Num_Months)).Formula = "= IFERROR(INDEX(Sub_Resource_Lookup_ID,MATCH($A2,Sub_Resource_Yaxis,0),MATCH(VLOOKUP(C$1,Setup_POP_RangeLookup,3,TRUE),Sub_Resource_Xaxis,0)),0)"[/B][/I][/U]
                    End If
ExitTheSub:
    Application.GoTo Dest_Sh.Cells(1)
    ActiveWindow.DisplayGridlines = False
End Sub
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
Hi, you could try replacing those bold and underlined sections with:

Code:
With Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months))
    .Formula = Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months)).Formula = "=DATE(YEAR(C$1),MONTH(C$1)+1,DAY(C$1))"
    .Value = .Value
End With
And:

Code:
With Dest_Sh.Range(Cells(2, 3), Cells(Dest_End_Row_2, 2 + Num_Months))
    .Formula = "= IFERROR(INDEX(Sub_Resource_Lookup_ID,MATCH($A2,Sub_Resource_Yaxis,0),MATCH(VLOOKUP(C$1,Setup_POP_RangeLookup,3,TRUE),Sub_Resource_Xaxis,0)),0)"
    .Value = .Value
End With
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hmmm this didn't work. It broke and says: "Run-time error 13: Type mismatch after it pasted the first formula in cell C1.

I think the issue is that the formula is for the column headers (months)... so C1 is Jan-1-2016, then I need the macro to drag the formula all the way right so each cell references off the previous cell

C1 = Jan-1-2016
D1 = Feb-1-2016
E1 = Mar-1-2016
F1 = Apr-1-2016
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
Sorry, the first code should be:

Code:
With Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months))
    .Formula = "=DATE(YEAR(C$1),MONTH(C$1)+1,DAY(C$1))"
    .Value = .Value
End With
 

Forum statistics

Threads
1,082,344
Messages
5,364,810
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top