Code to extract data equal to a ceratain values

[howard]

I have data on sheet3 on Columns A to M. The values are in Column K2 onwards

I would like to extract the data to a separate sheet making up a certain value

For eg if I want to find what items make of the value of say 2469147.72 on sheet3, I would like to be able to input this value in a message box and the items making of this value are extracted to sheet 4 from Col A to M


Your assistance in this regard is most appreciated

 

[Myrko]

Hi Howard,

Do these values which are to be checked appear only in K column or from K to M in any cell (and any of those should be checked versus the value you provide)?

 

[howard]

Thanks for the reply Myrko

The values only exist in Col K. If a combination of these values make up the value required then all cols containing the data that makes of the desired value to be extracted to sheet4. If the various combinations do not make up the desired value for eg 2469147.72 then message box to advise value doe not make up desired value

 

[Myrko]

Sub Extraction()


Dim i, j As Integer
Dim myValue As Variant


Application.ScreenUpdating = False
j = 0


On Error Resume Next
x = Sheets("Extracted").Range("A1").Value
If Err <> 0 Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Extracted"
Else
Sheets("Extracted").Cells.Clear
End If
On Error GoTo 0


myValue = InputBox("Please provide the minimum value to be extracted:", "Extraction tool", 2469147.72)


myValue = Format(myValue, 0)


Sheets("Sheet3").Select
Range("A1:M1").Copy
Sheets("Extracted").Select
Sheets("Extracted").Range("A1:M1").PasteSpecial xlPasteValues


Sheets("Sheet3").Select


For i = 2 To Range("K1000000").End(xlUp).Row
Sheets("Sheet3").Select
If Format(Range("K" & i).Value, 0) >= myValue Then
Range("A" & i & ":M" & i).Copy
Sheets("Extracted").Select
Range("A" & Range("A1000000").End(xlUp).Row + 1).PasteSpecial xlPasteValues
j = j + 1
End If
Next i


MsgBox "Total number of extracted rows is: " & j
Application.ScreenUpdating = True
End Sub


I assumed that you have all rows in K column of Sheet3 populated, that the Sheet3 exists. Let me know if this works as you've planned.

 

[howard]

Thanks for the code. Going out now , but will test tomorrow & let you know if it works

 

[Myrko]

Feel free to poke via private message when you come with your testing results. It's weekend and I might miss a regular "reply" to the topic

 

[howard]

Thanks for the help. The incorrect values are being extracted to sheet ""Extracted" The total of the values extracted is far higher than what I selected i.e 2469795.18

I have also sent you a Private Message