Macro Cell Referencing 1

[mickeyg]

Hi all,
I have a worksheet where I enter in the SKU and want to populate the Item #, Description, and Qty.

I am having two difficulties.
1) trying to use the vlookup function to add the item # and description
=VLOOKUP(A21,BathSecretItems!A3:G27,2,FALSE) + VLOOKUP(A21,BathSecretItems!A3:G27,3,FALSE)
I want to see 6693 Bath Pillow. Instead I receive the #VALUE! error.

2) referencing any cells to the right or down from my active cell. After populating the Item # and description, I want to go to the next row and over three columns to fill the qty.

Thank you,
Mickey

 

[Goska]

1)Try this. Replace your + with a &. + is usually for math, and so it hte #value error. & is for text strings

=VLOOKUP(A21,BathSecretItems!A3:G27,2,FALSE) & VLOOKUP(A21,BathSecretItems!A3:G27,3,FALSE)

if you need a space in the middle, use this:
=VLOOKUP(A21,BathSecretItems!A3:G27,2,FALSE) & " " & VLOOKUP(A21,BathSecretItems!A3:G27,3,FALSE)


2)I don't quite understand what you mean. Do you mean taht you want a quantity automatically filled in? Or do you mean that you want the cursor to over there after you get done typing?

 

[mickeyg]

Goska,
The concatenation works great.

One row under my item #/description, I want to use the Vlookup function to return my quantity and hard code "Cases of" and "each".

So my final output would like this:
6693Bath Pillow - Asst.
Cases of 99 each

FYI--My lookup data looks like this:
SKU # Item # Item Description Qty
========================================
491004 6693 Bath Pillow - Asst. 99


Any suggestions are welcome!
Mickey

 

[Goska]

Since it seems like these quantities are going to be the same all the time(unless your case size gets changed for an item), why don't you use another Vlookup function?

Somthing like

="Cases of " & VLOOKUP(A21,BathSecretItems!A3:G27,4,FALSE)& " Each"

 

[mickeyg]

I have entered the formulas in the cells and it works great, but I really want to automate this with a macro.

User Action:
Enter SKU...press Ctrl-Shift-D

Pseudocode:
1. Populate description one cell to the right of ActiveCell.
2. Populate qty two cells to the right and one row down from ActiveCell.

I know how to get my ActiveCell value, but am stuck after that.

Mickey

 

[Goska]

What I usually do for this kind of thins is write a VBA sub() and attach it to a Ctrl-Letter. I have not had any luck combining it with a shift, not that I have tried really hard at that. Here is some code that should work for you. Read my notes and try it.


Sub test()
Dim i As Integer
Dim CellRef As String
Dim Frmula As String
'GET THE NUMBER OF THE ROW FROM YOUR ACTIVE CELL AND STORE IT IN i

'I AM PUTTING THIS ALL IN COLUMN Y&Z
'ASSUMING THAT ACTIVE CELL IS IN X
CellRef = "Y" & i
Frmula = "VLOOKUP(A" & i & ",BathSecretItems!A3:G27,2,FALSE)" & " " & "VLOOKUP(A" & i & ",BathSecretItems!A3:G27,3,FALSE)"
i = i + 1
CellRef = "Z" & i
Frmula = "=" & Chr$(34) & "Cases of " & Chr$(34) & "VLOOKUP(A" & i & ",BathSecretItems!A3:G27,4,FALSE)" & Chr$(34) & " Each"
'CHR$(34) IS QUOTATION MARK
'"Cases of " & VLOOKUP(A21,BathSecretItems!A3:G27,4,FALSE)& " Each"
End Sub