How to read decimal number from cell and copy all

Question

I am trying to read a number from a Cell (this number changes so I have referenced the cell) and then copy it to a new sheet. There are no issues with the reading or copying. The issue I have is that the number that is being read from another cell doesn't work with decimal numbers. I can read cells with all whole numbers and copy their respective rows but just not decimals. I have tried a work around solution that rounded the numbers to whole numbers but later found this just wont work for the accuracy required in my solution.

A Snippet of code is below:

With ws.Result.Range ("A1:F" & .Cells(.Rows.Count, "F").End(xlUp).Row
.AutoFilter Field := 6, Criteria1 := wsResult.Range("J1") ' checks row F to see 
whether the number in cell J1 matches any in row F

If Application.WorksheetFunction.Subtotal(103, .Columns(1)>1 Then .
Offset(1). Resize (.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy 
Destination:= Main.Range("A22") ' Paste all rows starting at A22 on the main page.

I know the rest of the code works as planned its purely just being able to get the numbers that are decimal to work.

Any help would greatly be appreciated.

Answer 1

to get around any decimal formatting issue, you could try the following code (explanations in comments):

With wsResult ' be sure 'wsResult' is properly set to a valid 'WorkSheet' object before reaching this line
    With .Range("A1:F" & .Cells(.Rows.Count, "F").End(xlUp).Row) 'reference its columns A:F cells from row 1 (header) down to last not empty one in column "F"
        .AutoFilter field:=6, Criteria1:=">=" & Round(.Range("J1").Value2, 3) - 0.0001, Operator:=xlAnd, Criteria2:="<=" & Round(.Range("J1").Value2, 3) + 0.0001 ' filter referenced cells on 6th column with content in a range of 0.0001 from referenced range "J1" cell content rounded to three decimals
        If Application.WorksheetFunction.Subtotal(103, .Columns(1)) > 1 Then .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=Main.Range("A22") ' if any filtered cell other than the header then copy and paste them to 'Main' worksheet starting from its A22 cell
    End With
    .AutoFilterMode = False
End With

and should your actual cell content decimal separator be a dot (.), then be sure to substitute the comma (,) decimal separator read from "J1" cell content with a dot by changing:

        .AutoFilter field:=6, Criteria1:=">=" & Round(.Range("J1").Value2, 3) - 0.0001, Operator:=xlAnd, Criteria2:="<=" & Round(.Range("J1").Value2, 3) + 0.0001 ' filter referenced cells on 6th column with content in a range of 0.0001 from referenced range "J1" cell content rounded to three decimals

to

        .AutoFilter field:=6, Criteria1:=">=" & Replace(Round(.Range("J1").Value2, 3) - 0.0001, ",", "."), Operator:=xlAnd, Criteria2:="<=" & Replace(Round(.Range("J1").Value2, 3) + 0.0001, ",", ".") ' filter referenced cells on 6th column with content in a range of 0.0001 from referenced range "J1" cell content rounded to three decimals

as a side note, just be aware that .Range("J1") is pointing at the cell in the first row and 10th column of the referenced range. In this case your referenced range starts from cell "A1" of referenced wsResult worksheet, so that is equivalent towsResult.Range("J1").