I am trying to write a macro using if then goto offset

[svis89]

I recently took a job that requires me to brush up on my 12 year old macro skills, i need to check a list for part number then go to the part number and offset to the J column so that I can post the bought values on particular part number in that area so it can all calculate correctly i have a lot of changes to make to my existing macro, i am creating a input sheet for when parts are bought or sold they can input the value hit submit it will then copy value go to count page and find the part number they provided next to value and then offset from the part number to the J column

 

[Veloxy]

do you want it to select a cell if it finds one with the same value as B2 within the range B2:B23?

 

[svis89]

do you want it to select a cell if it finds one with the same value as B2 within the range B2:B23?

Yes and then offset to the J column where it will paste

 

[svis89]

Yes and then offset to the J column where it will paste

Yes and then offset to the J column where it will paste

It is B2:B23 on a different sheet (Count sheet)

 

[Veloxy]

Try This

Sub Bought_1()
Range("D2").Activate
If ActiveCell = vbNullString Then Exit Sub
Selection.Copy
RngB = Range("B2").Value

Sheets("Count sheet").Activate
For Each cell In Range("B2:B23")
If cell.Value = RngB Then
pp = pp + 1
End If
Next

If pp = 1 Then
Range("J2").Value = RngB
Sheets("Inventory management").Activate
Application.CutCopyMode = False
Range("A2").Copy
Sheets("Count Sheet").Activate
Range("J1").Paste
Columns("J:J").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
Range("J2").Activate
Sheets("Inventory management").Activate
Range("D2").ClearContents
Else
MsgBox "Nothing"

End If

End Sub

 

[Veloxy]

Sorry Range("J1").Paste is Range("J1").PasteSpecial

 

[svis89]

Try This

Sub Bought_1()
Range("D2").Activate
If ActiveCell = vbNullString Then Exit Sub
Selection.Copy
RngB = Range("B2").Value

Sheets("Count sheet").Activate
For Each cell In Range("B2:B23")
If cell.Value = RngB Then
pp = pp + 1
End If
Next

If pp = 1 Then
Range("J2").Value = RngB
Sheets("Inventory management").Activate
Application.CutCopyMode = False
Range("A2").Copy
Sheets("Count Sheet").Activate
Range("J1").Paste
Columns("J:J").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
Range("J2").Activate
Sheets("Inventory management").Activate
Range("D2").ClearContents
Else
MsgBox "Nothing"

End If

End Sub

it is copying B2 instead of D2 so i think i need to add
Sheets("Inventory management").Activate
Range("D2).Activate
Selection.Copy
Sheets("Count sheet").Activate
Range(J2).PasteSpecial


Range("J2").Value = RngB i think this line is the problem

i added clips from sheets so you can see what I'm doing

 

[Veloxy]

Ok, so you want it to update the values in the other spreadsheet and the old values go to column H onwards?

 

[svis89]

Sub Bought_1()
Range("D2").Activate
If ActiveCell = vbNullString Then Exit Sub
Selection.Copy
RngB = Range("B2").Value

Sheets("Count sheet").Activate
For Each cell In Range("A2:A23")
If cell.Value = RngB Then
pp = pp + 1
End If
Next

If pp = 1 Then
Sheets("Inventory management").Activate
Range("D2").Activate
Selection.Copy
Sheets("Count sheet").Activate
Range("J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Inventory management").Activate
Application.CutCopyMode = False
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Count sheet").Select
Range("J1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
Range("J2").Select
Sheets("Inventory management").Select
Range("D2").Select
Selection.ClearContents
Else
MsgBox "Nothing"

End If

End Sub

this is what i ended up with that solved it all thank you for your help

Ok, so you want it to update the values in the other spreadsheet and the old values go to column H onwards?