vb

[sna]

Hi dear
I need your help how to fix my VBA code without using helper column to fetch data from cell c2 and save .

Code: Select all

Sub test()
Dim lr As long ,s As String,X As Long 
lr=Sheet1.Cells(Sheet1.Rows.Count,1).End(xlUp).Row+1
s=Sheet1.Range("B2").Text
X=Application.Match(s,Sheet1.Range("A:A"),0)
Sheet1.Range("D"& X).Value=Sheet1.Range("C2").Value
Msgbox "data saved!"
[B2:C2].Clearcontents

End Sub

[snasui]

You can use this code for replace value in column D with the value in cell C2.

Code: Select all

Sub Trust()
    Dim LastRow As Long, S As String, fRng As Long
    Dim r As Range
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    S = Sheet1.Range("B2").Text
'    fRng = Application.WorksheetFunction.Match(S, Sheet1.Range("A:A"), 0)
    For Each r In Range("b5:b" & LastRow)
        If r.Value & r.Offset(0, 1).Value = Range("b2").Value Then
'            Range("D" & fRng) = Sheet1.Range("C2").Value
            r.Offset(0, 2).Value = Sheet1.Range("c2").Value
            Exit For
        End If
    Next r
    MsgBox "DATA SAVED"
    Range("B2:C2").ClearContents

End Sub

[sna]

Tks, always helpful