Filtered Row and Input box

[gnissen]

Hi All, this is my first time using this, i hope someone can help.

I am filtering data using the following code
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Unprotect "121"
sh.Range("B7:B2000").AutoFilter Field:=1, Criteria1:="=*" & InputBox("Reset Sheet", "Part Number Search", "*")
This will filter all my records and give me a single line of data, on row 7 "Perfect"

The problem is when i filter the data it filters the top rows so the data could be on row 7 or row 5o.

I then need to add a second input box in to allow the user to enter a value using the following code
myVal = InputBox("Enter New Count")
Range("H7").Value = myVal

BUT this is the trick, how do i enter the new value on the filtered line when the filtered line could now be "H50" or "Hxxxxx"
Regards
Greg

 

[AlphaFrog]

sh.Range("H" & sh.Range("B8:B2000").SpecialCells(xlCellTypeVisible).Row).Value = MyVal

 

[gnissen]

sh.Range("H" & sh.Range("B8:B2000").SpecialCells(xlCellTypeVisible).Row).Value = MyVal

Hi and thank you, can you please confirm the code as it did not ask me for the part number first. or have i added it to the wrong place?

Dim newval As Variant
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Unprotect "121"

Original code 'sh.Range("D6:D2000").AutoFilter Field:=1, Criteria1:=InputBox("Please Enter a Part# ", "Part Number Search")
Your code should i replace this with yours? sh.Range("H" & sh.Range("B8:B2000").SpecialCells(xlCellTypeVisible).Row).Value = myVal

'Application.GoTo Range("H7").SpecialCells(xlCellTypeVisible).Cells(1)

myVal = InputBox("Enter New Count")
Range("H7").Value = myVal


Regards
Greg

 

[AlphaFrog]

Dim newval As Variant
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Unprotect "121"

sh.Range("D6:D2000").AutoFilter Field:=1, Criteria1:=InputBox("Please Enter a Part# ", "Part Number Search")

myVal = InputBox("Enter New Count")
sh.Range("H" & sh.Range("D7:D2000").SpecialCells(xlCellTypeVisible).Row).Value = myVal

 

[gnissen]

Dim newval As Variant Dim sh As Worksheet Set sh = ActiveSheet sh.Unprotect "121" sh.Range("D6:D2000").AutoFilter Field:=1, Criteria1:=InputBox("Please Enter a Part# ", "Part Number Search") myVal = InputBox("Enter New Count") sh.Range("H" & sh.Range("D7:D2000").SpecialCells(xlCellTypeVisible).Row).Value = myVal

It works perfect
Many Thanks for your help
Greg

 

[gnissen]

It works perfect
Many Thanks for your help
Greg

could i impose and ask if you could help with another thing.
Once the new value has been entered i need take column "I" being the new total from that row and move it to "G" and delete "H"
this will give me a previous count +the new count and delete "H" ready for a new value

 

[AlphaFrog]

Why not have the macro just add myVal to "G"?

This adds myVal directly to G

myVal = InputBox("Enter New Count")
with sh.Range("G" & sh.Range("D7:D2000").SpecialCells(xlCellTypeVisible).Row)
    .Value = .Value + myVal
End With

 

[gnissen]

Why not have the macro just add myVal to "G"?

This adds myVal directly to G

myVal = InputBox("Enter New Count")
with sh.Range("G" & sh.Range("D7:D2000").SpecialCells(xlCellTypeVisible).Row)
    .Value = .Value + myVal
End With

Hi the idea was to have the the prev count "G" plus the new count "H" then "I" will be the total of the two. Then I will copy "I" to prev "G" Then delete "H". This will give me a new prev so I can repeat the process if multiple items are scanned.

 

[gnissen]

Hi the idea was to have the the prev count "G" plus the new count "H" then "I" will be the total of the two. Then I will copy "I" to prev "G" Then delete "H". This will give me a new prev so I can repeat the process if multiple items are scanned.

Thank you vey much, its all working perfect now
Many Many Thanks
Greg

 

[smozgur]

Thank you vey much, its all working perfect now
Many Many Thanks
Greg

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.