PDA

View Full Version : Probably an easy question for you VBA pros!



Ruppy
05-07-2013, 09:49 AM
First off, I am completely new to VBA, so hopefully my question makes some sense. I am trying to edit a macro in Excel 2010 to make a part number equal a purchase order #. Our PO numbers are going to remain the same for the rest of the year and I when this macro is run, I'm wanting it to fill in the PO number in a certain column. For example, this is what the macro looks like for 1 part number.

Sub e4822153()
Cells.Find(What:="4822153", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "71068"
ActiveCell.Offset(0, 16).Range("A1").Select
ActiveCell.FormulaR1C1 = "22 mil"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "8000"
ActiveCell.Offset(0, -16).Range("A1").Select

So when I do an Alt+F8 at E7 and run the macro, it will fill in information at E7,U7, and V7. I would like a static number to populate in J7. Is there a few lines that I could add to get the data to end up in J7? I was able to get the data to appear in my Excel sheet, but I am not able to figure out how to make a piece of data populate exactly in the row/column that I am wanting.

Thanks in advance for any help!

SamT
05-07-2013, 02:41 PM
This is a duplicate of you code's functionality.
Sub e4822153()
Dim Rw As Long
R = Find(What:="4822153", After:=Selection, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
Cells(Rw, "D") = "71068" '"D" = Offset -1 from "E"
Cells(Rw, "U") = "22 mil" '"U" = Offset 16 from "E"
Cells(Rw, "F") = "8000" '"F" = Offset 1 from "E"
Cells(Rw, "V") = ???
Cell(Rw, "J") = Static Number
'ActiveCell.Offset(0, -16) There is no column 16 to the left of "E"