VBA help - How to post data into specific rows/columns based on a rows reference number

[Steve Herrington]

Hi everyone,

First time poster. Long time reader of Chandoo. I was wondering if I could get help with something I am working on.

What I am trying to do is create a macro that will update specific cells of a data sheet. For example in one tab I have a form where you can enter a reference number, and then enter information. What I want is a macro that will look up that reference number in the data sheet, and then find the relevant cell along that row and paste the data that is supplied in the form. And then, for subsequent updates of information to copy to the adjacent cell of that row.

I have attached a crude example of what I am trying to achieve, but I'm so far drawing a blank. Any help would be appreciated!

 

[Hui]

Steve

Firstly, Welcome to the Chandoo.org Forums

I think the following will do what you want

Sub Shift_Data()
Dim Findwhat As String
Dim c As Range
Worksheets("Form").Range("B3").Copy
Worksheets("Data").Select
Findwhat = Worksheets("Form").Range("B2").Text
With Range("a:a")
  Set c = .Find(Findwhat, LookIn:=xlValues)
End With

c.End(xlToRight).Offset(, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
Application.CutCopyMode = False

Worksheets("Form").Range("B2:D3").ClearContents
Worksheets("Form").Select
Range("B2:D2").Select

End Sub

Copy and paste the text into a code module in VBA
Then link the button to the code