Belleke
Well-Known Member
I have this code
But in column A of the sheets where the info comes from there are formulas and i get this
My question, how can i change this code that I get the values instead?
See example.
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
Code:
=IF(#REF!="",""&#REF!,LEFT(#REF!, FIND(" ",#REF!)-1) & "."&COUNTIF(#REF!,#REF!))
See example.