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

svis89

Board Regular
Joined
Oct 19, 2021
Messages
57
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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
 
Last edited by a moderator:
What exactly do you want returned, you can't use activecell because the cell hasn't been activated/selected and doesn't need to be?
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
If you just want it 2 columns over then it is either
VBA Code:
 For Each cell In Sheets("Count sheet").Range("A2:A23")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2)  = Sheets("Inventory management").Range("E2")
 Next
or
VBA Code:
 For Each cell In Sheets("Count sheet").Range("A2:A23")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol + 2)  = Sheets("Inventory management").Range("E2")
 Next
 
Upvote 0
What exactly do you want returned?

If you just want it 2 columns over then it is either
VBA Code:
 For Each cell In Sheets("Count sheet").Range("A2:A23")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2)  = Sheets("Inventory management").Range("E2")
 Next
or
VBA Code:
 For Each cell In Sheets("Count sheet").Range("A2:A23")
        If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol + 2)  = Sheets("Inventory management").Range("E2")
 Next
I already tried the both of those they put it onto the inventory sheet instead of the count sheet and now when i swap back to the other code i cant figure out where its putting the data and its adding a column to E2 on count sheet instead of near sold tag...
 
Upvote 0
Are you sure that you used the exact code above as
Rich (BB code):
Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2)
Cannot put the value in the inventory sheet
 
Upvote 0
I already tried the both of those they put it onto the inventory sheet instead of the count sheet and now when i swap back to the other code i cant figure out where its putting the data and its adding a column to E2 on count sheet instead of near sold tag...
Okay it is posting it on to inventory sheet instead of count sheet,
For Each cell In Sheets("Count sheet").Range("A2:A23")
If cell.Value = RngB Then Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("E2")
all of this is happening on the Inventory sheet instead of count sheet
 
Upvote 0
Are you sure that you used the exact code above as
Rich (BB code):
Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2)
Cannot put the value in the inventory sheet
VBA Code:
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).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
    
    Sheets("Count sheet").Columns(mycol).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents

End Sub
 
Upvote 0
VBA Code:
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).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
   
    Sheets("Count sheet").Columns(mycol).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
  
    Sheets("Inventory management").Range("E2").ClearContents

End Sub
Okay i see what i missed
 
Upvote 0
VBA Code:
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 Sheets("Count sheet").Cells(cell.Row, mycol).Offset(, 2) = Sheets("Inventory management").Range("E2")
    Next
    
    Sheets("Count sheet").Columns(mycol).Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("E2").ClearContents

End Sub

Do you see anything else i missed
 
Upvote 0
Run-time error '91':
object variable or with block variable not set

when i run the last code i posted this line is highlighted yellow and i get the above error
mycol = Rows(1).Find("Sold", , xlValues, , xlByColumns, xlPrevious).Column
 
Upvote 0
That means that it is not finding the word Sold in row 1 of the sheet that is active. Is Sheets("Count sheet") the Active sheet when you run the code?
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top