• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Values instead of formulas

Belleke

Well-Known Member
I have this code
Code:
Private Sub Cmd1_Click()

Application.ScreenUpdating = False

Set j20 = Sheets("JAP 2020")
lrJ20 = j20.Cells(Rows.Count, 1).End(xlUp).Row
If lrJ20 < 5 Then lrJ20 = 5
j20.Rows(5 & ":" & lrJ20).ClearContents
    
Set R = Sheets("Resultaten")
GoSub kopieer
    
Set R = Sheets("Resultaten (2)")
GoSub kopieer

For rij = lrJ20 To 5 Step -1
    If j20.Cells(rij, 2) = "" And j20.Cells(rij - 1, 2) = "" Then
        j20.Rows(rij - 1).Delete
    End If
Next rij

Exit Sub

kopieer:
With R
    lrR1 = .Cells(Rows.Count, 4).End(xlUp).Row
    For i = 8 To lrR1
        If .Cells(i, 4) <> "" Then
            lrJ20 = j20.Cells(Rows.Count, 1).End(xlUp).Row + 1
            If .Cells(i, 5) = "" Then
                If InStr(1, .Cells(i, 4), ".") = 0 And .Cells(i, 7) = "" And IsNumeric(Left(.Cells(i, 4), 1)) Then
                    .Range("D" & i).Copy j20.Range("A" & lrJ20)
                End If
            Else
                If UCase(.Range("G" & i)) = "X" Then
                  .Range("D" & i & ":E" & i).Copy j20.Range("A" & lrJ20 & ":B" & lrJ20)
                End If
            End If
        End If
    Next i
End With
Return

End Sub
But in column A of the sheets where the info comes from there are formulas and i get this
Code:
=IF(#REF!="",""&#REF!,LEFT(#REF!, FIND(" ",#REF!)-1) & "."&COUNTIF(#REF!,#REF!))
My question, how can i change this code that I get the values instead?
See example.
 

Attachments

vletm

Excel Ninja
Belleke
a) Where is even one formula in A-column?
b) Do not clear A-column formulas ... j20.Rows(5 & ":" & lrJ20).ClearContents
 

Belleke

Well-Known Member
In the real sheet there are formulas, in the example there are none.
I need to clear this or I get all the main sections even if there are no lines under them.
 

Belleke

Well-Known Member
The goal is to copy the rows that have an x in column G from the result sheets to the Jabpsheet, In the result sheets, there are formulas in column A like the formula above. But in the Jap sheet the formulas should be replaced with the values.
I used your code from # 4 but then I get what you see in post #5.
 

Belleke

Well-Known Member
I added this line of code and I get what you see in #5.
Code:
j20.Cells(lrJ20, "A") = .Cells(i, "D")
 

vletm

Excel Ninja
Did You add it as below, as can You can see below picture, as I've tried to verify many times?
Screenshot 2019-11-06 at 12.15.31.png
 

Marc L

Excel Ninja
As per forum rules so explain why did you create in the Excel formula forum a VBA question as you well know the VBA forum ?!​
As there is a sample in the thread of my last post in the appropriate VBA forum …​
 

Marc L

Excel Ninja
The one from Ateed Ali whatever in the initial code or my revamped one … More samples via the top Search button …​
 

Belleke

Well-Known Member
That is not what I want, I want to have some help with this code to solve the formula problem.
And can someone move this thread to the VBA section, I was not aware I was in the wrong section.
 

Marc L

Excel Ninja
That is not what I want
Wrong as this is exactly what you need just using any value property !​
Another way is the Copy and PasteSpecial methods like when activating the Macro recorder and operating manually …​
 

Marc L

Excel Ninja
As a VBA beginner reminder :​
to copy a range to another place the easy way is SourceRange.Copy DestinationTopLeftCell …​
But when the source contains some formulas and only the values must allocate the destination range, two ways :​
  • SourceRange.Copy first then DestinationTopLeftCell.PasteSpecial methods like when operating manually,
    easy with the Macro Recorder or just reading the VBA inner help …

  • Using a range value property like DestinationRange.Value2 = SourceRange.Value2
 
Top