I.E A2=D2 on another page i need my macro to go from A2 to J column

[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

So if A2=RngB then go to J2
if A2:A23= RngB it needs to go to the J column for that row
i was thinking once it identifies the RngB to offset to the J but i could not think of how it could go back to the "Inventory management" copy D4 and then return to the correct cell

 

[svis89]

Inventory List.xlsm
	B	C	D	E	F	G	H	I	J			M	N	O					T	U	V	W
1	Part classification	In stock	bought	Sold	Defect		Bought					Sold							Defect
2	Sections	0	2650	2400	250		2650	2650				2400	2400						250	250
3	Buckets	0	0	0	0		0					0							0
4	Wires	0	0	0	0		0					0							0
5		0	0	0	0		0					0							0
6		0	0	0	0		0					0							0
7		0	0	0	0		0					0							0
8		0	0	0	0		0					0							0
9		0	0	0	0		0					0							0
10		0	0	0	0		0					0							0
11		0	0	0	0		0					0							0
12		0	0	0	0		0					0							0
13		0	0	0	0		0					0							0
14		0	0	0	0		0					0							0
15		0	0	0	0		0					0							0
16		0	0	0	0		0					0							0
17		0	0	0	0		0					0							0
18		0	0	0	0		0					0							0
19		0	0	0	0		0					0							0
20		0	0	0	0		0					0							0
21		0	0	0	0		0					0							0
22		0	0	0	0		0					0							0
23		0	0	0	0		0					0							0
Count sheet

Cell Formulas
Range	Formula
C2:C23	C2	=[@bought]-[@Sold]-[@Defect]
D2:D23	D2	=H2
E2:E23	E2	=M2
F2:F23	F2	=T2
H2:H23	H2	=SUM(I2:L2)
M2:M23	M2	=SUM(N2:R2)
T2	T2	=SUM(U2:AG2)
T3:T23	T3	=SUM(X3:X3)

 

[svis89]

Which of Bought, Sold, Defect is the column for CngB? also is it the first, last or other instance of the word and also...
Can you also put up a screenshot of your spreadsheet using the boards XL2BB addin rather than an image so we can copy it into Excel please

i added the Mini sheet the problem is that each time i hit submit for bought the column for sold and defect change, i have the same word in the table as headers i can change those to added, removed and scrapped

 

[MARK858]

I need to know what we are looking for in the find (What word and what instance [I am assuming at the moment the last instance]), all the other columns can be referenced by an offset from that column but I need to know what to look for to get the starting column, without that then I can't provide any help.
Am I lookin for "Bought"? and if so what instance?

 

[svis89]

I need to know what we are looking for in the find (What word and what instance [I am assuming at the moment the last instance]), all the other columns can be referenced by an offset from that column but I need to know what to look for to get the starting column, without that then I can't provide any help.
Am I lookin for "Bought"? and if so what instance?

Sold in the above minisheet it is M1

I Changed E1 to removed from inventory so M1 is the only sold in my sheet now

 

[MARK858]

As a demo how you could reference them...

Sub Test1()
Dim mycol As Long, CngB As Range

mycol = Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column

Set CngB = Columns(mycol)

MsgBox "Column with Sold is column number " & CngB.Column & vbLf & "Column with Bought is column number " & CngB.Offset(, -5).Column & vbLf & "Column with Defect is column number " & CngB.Offset(, 7).Column
End Sub

or just

Sub Test1a()
Dim mycol As Long

mycol = Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column

MsgBox "Column with Sold is column number " & mycol & vbLf & "Column with Bought is column number " & Columns(mycol - 5).Column & vbLf & "Column with Defect is column number " & Columns(mycol + 7).Column

End Sub

You could get just the column number with mycol + 7 but using Columns(mycol + 7) demonstrates how to reference the actual column rather than just the column number

 

[svis89]

As a demo how you could reference them...

Sub Test1()
Dim mycol As Long, CngB As Range

mycol = Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column

Set CngB = Columns(mycol)

MsgBox "Column with Sold is column number " & CngB.Column & vbLf & "Column with Bought is column number " & CngB.Offset(, -5).Column & vbLf & "Column with Defect is column number " & CngB.Offset(, 7).Column
End Sub

or just

Sub Test1a()
Dim mycol As Long

mycol = Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column

MsgBox "Column with Sold is column number " & mycol & vbLf & "Column with Bought is column number " & Columns(mycol - 5).Column & vbLf & "Column with Defect is column number " & Columns(mycol + 7).Column

End Sub

You could get just the column number with mycol + 7 but using Columns(mycol + 7) demonstrates how to reference the actual column rather than just the column number

okay i have been trying to write the Dim mycol into a range selection i need to reference RngB to tell the macro what row to paste in then use Dim mycol to tell the macro what column of the row its supposed to paste E2 im making sure we are on same page here.. i thought Dim mycol was for getting references not deciding a range but i havent done anything with macro's since 2008 in highschool so i am very rusty (hmm now where did i lay down that macro)

 

[MARK858]

Cells(cell.Row, mycol)

gives you the intersect (using cell from my earlier code).

 

[svis89]

[/
Sub Sold2()
    Dim cell As Range, RngB
    Dim mycol As Long, CngB As Range
    
    mycol = Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column
    Set CngB = Columns(mycol)
    
    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A23")
    
        If cell.Value = RngB Then Cells=(cell.Row , mycol)


    Next
    
    Sheets("Count sheet").Columns(mycol).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents

End Sub
CODE]
So im getting a syntax error and its telling me i need a ) after cell.Row

 

[MARK858]

Remove the = sign after Cells, I didn't have one there

 

[svis89]

Remove the = sign after Cells, I didn't have one there

then i get a syntax error