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

svis89

New Member
Joined
Oct 19, 2021
Messages
43
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:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

svis89

New Member
Joined
Oct 19, 2021
Messages
43
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
VBA Code:
[/CODESub 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 .Select
    Offset(0,8)
    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

[CODE=vba]
so thats what i have been trying to get to work so far but i'm not sure about how to set the selection from above the offset so i dont have anything to use the offset on
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
i could not think of how it could go back to the "Inventory management" copy D4 and then return to the correct cell
You do it by not activating the sheets/cells, as a basic example (and assuming that RngB is on Sheets("Count sheet"))...

VBA Code:
Sub Bought_1test()
    Dim cell As Range, RngB

    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A23")
    
        If cell.Value = RngB Then cell.Offset(, 9).Value = _
                Sheets("Inventory management").Range("D2").Value
  
    Next
    
    Sheets("Count sheet").Columns("J:J").Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
   
    Sheets("Inventory management").Range("D2").ClearContents

End Sub
 

svis89

New Member
Joined
Oct 19, 2021
Messages
43
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
You do it by not activating the sheets/cells, as a basic example (and assuming that RngB is on Sheets("Count sheet"))...

VBA Code:
Sub Bought_1test()
    Dim cell As Range, RngB

    RngB = Sheets("Inventory management").Range("B2").Value


    For Each cell In Sheets("Count sheet").Range("A2:A23")
   
        If cell.Value = RngB Then cell.Offset(, 9).Value = _
                Sheets("Inventory management").Range("D2").Value
 
    Next
   
    Sheets("Count sheet").Columns("J:J").Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove
  
    Sheets("Inventory management").Range("D2").ClearContents

End Sub
Thank you so much i did not understand how to do this part at all and i love how much you cleaned up the code
If cell.Value = RngB Then cell.Offset(, 9).Value = _
Sheets("Inventory management").Range("D2").Value
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

You're welcome
 

svis89

New Member
Joined
Oct 19, 2021
Messages
43
Office Version
  1. 365
Platform
  1. Windows
So i ran into a slight problem i was going to simply implement this into each macro im running for bought, sold and defects on this inventory screen put each time it is adding a column which it needs to, So can i implement a search function like i did before to find RngB to find the correct column so im going from inventory management to the count sheet...i.e. RngB find the row and CngB finds column

IF i need to start a new thread let me know and i will
 

Attachments

  • Count sheet.png
    Count sheet.png
    25.3 KB · Views: 6
  • Inventory management.png
    Inventory management.png
    13.2 KB · Views: 7

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Define exactly what you would do to find CngB manually please.
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.
 

svis89

New Member
Joined
Oct 19, 2021
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Define exactly what you would do to find CngB manually please
i would check row one to find Bought, Sold, Defect then offset two columns to the right and down till i found the Row that RngB is on
 

svis89

New Member
Joined
Oct 19, 2021
Messages
43
Office Version
  1. 365
Platform
  1. Windows
i would check row one to find Bought, Sold, Defect then offset two columns to the right and down till i found the Row that RngB is on
i can copy the Bought, Sold, Defect down the entire column so that it can offset RngB until it finds Bought, Sold, Defect then Offset to the right twice if that would be easier
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,304
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Forum statistics

Threads
1,147,925
Messages
5,743,897
Members
423,824
Latest member
leahpatterson

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
Top