Find and Replace Row
 

Hello,

I am using "Code 1" below to search through column (A) and find a specific
value which matches the contents of cboMonth on my user form. If a match is
found, the code alerts with a message. What i would like to be able to do is
if a match is found, ask the user if they would like to replace the found row
with the new values entered on the UserForm.

Can anyone provide me wiht the code i need to replace the contents of a row?

Code 1: Search for and alert if value is found:

Private Sub CommandButton1_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim msg As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("HQ Input Log")
Set Rng = SH.Columns("A:A")
sStr = Me.txtCriteria1.Value

If Application.CountIf(Rng, sStr) Then
msg = sStr & " found"
Else
msg = sStr & " not found"
End If

MsgBox Prompt:=msg

End Sub


--
Carlee

Find and Replace Row
 

some like theese:

Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim msg As String
Dim rw

Set WB = ThisWorkbook
Set SH = WB.Sheets("HQ Input Log")
Set Rng = SH.Columns("A:A")
sStr = Me.txtCriteria1.Value

rw = SH.Range("A:A").Find(sStr, LookIn:=xlValues).Row

If MsgBox("Change found value ? " & sStr & " to " & "New!!! ", vbYesNo)
= vbYes Then
SH.Cells(rw, 1) = Me.txt.Value 'change to urs *********
End If




"Carlee" skrev:

Hello,

I am using "Code 1" below to search through column (A) and find a specific
value which matches the contents of cboMonth on my user form. If a match is
found, the code alerts with a message. What i would like to be able to do is
if a match is found, ask the user if they would like to replace the found row
with the new values entered on the UserForm.

Can anyone provide me wiht the code i need to replace the contents of a row?

Code 1: Search for and alert if value is found:

Private Sub CommandButton1_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim msg As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("HQ Input Log")
Set Rng = SH.Columns("A:A")
sStr = Me.txtCriteria1.Value

If Application.CountIf(Rng, sStr) Then
msg = sStr & " found"
Else
msg = sStr & " not found"
End If

MsgBox Prompt:=msg

End Sub


--
Carlee