Change format

[Belleke]

Hello,
I have this code (works)
But I would like that the results of zoekD are formated as dd.mm.yyyy
And the results of zoekE are formated as financial with 2 decimals.
In fact it should keep the same format as the table where the results are copied from.
Please advice

Sub test()
Dim id As Range, cid As Range, result As Range
Dim idfind As Range
Dim j As Integer, k As Integer
Dim zoekC As Long
Dim zoekD As Long
Dim zoekE As Long

Range("B1").CurrentRegion.Sort key1:=Range("B1"), Header:=xlYes

Set id = Range(Range("B1"), Range("B1").End(xlDown))

Set result = Range("B1").End(xlDown).Offset(4, 0)
MsgBox result.Address
id.AdvancedFilter xlFilterCopy, , result, True
Set result = Range(result.Offset(1, 0), result.End(xlDown))

For Each cid In result
k = WorksheetFunction.CountIf(id, cid)

Set idfind = id.Find(what:=cid, lookat:=xlWhole)
For j = 1 To k
zoekC = idfind.Offset(j - 1, 0).Offset(0, 1)
zoekD = idfind.Offset(j - 1, 0).Offset(0, 2)
zoekE = idfind.Offset(j - 1, 0).Offset(0, 3)

Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = zoekC
Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = zoekD
Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = zoekE
Next j
Next cid



End Sub

 

[Luke M]

Be careful, you have zoekE define as a Long data type, so it will never have any decimal portion. But, we can certainly change the format which is done by changing the cell's format (not the variable, as the value isn't changing)

With Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1)
    .Value = zoekC
    'Guessing at this one
    .NumberFormat = "dd.mm.yyyy"
End With
With Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1)
    .Value = zoekD
    .NumberFormat = "dd.mm.yyyy"
End With
With Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1)
    .Value = zoekE
    .NumberFormat = "$#,##0.00"
End With

 

[Belleke]

Hello Luke,
Thank you for the fast reply but I am not getting the results that I want.
File included, maybe you can have a look at it.
Thank you