Hello there, MrExcel community!
I've spent a few days now trying to find the answer to the question I am about to pose but I am still missing some that I fear is very simple.
I have two adjacent columns of formula, one will return a row number from another sheet or "", the other will return a low numerical value if there is anything but "" in the adjacent cell. If the adjacent cell is "", this cell will also return "". So I end up with somthing like this:
A---B
23--2
35--1
36--1
72--2
What I would like to have is a bit a vba that will go down column B and if it finds a numerical value, store it in a variable and store the number in column A in a variable. The go to a separate sheet and create a new list with B*instances of A. So the new list would look like:
23
23
35
36
72
72
I believe I know how to do all of it but one thing, and that is hopping between cells containing any number in a column.
This is the code I have been using:
Range("B1:B1000").Find(XlSpecialCellsValue.xlNumbers, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Select
Which works fine for the number 1! Anything over 1 is just rudely ignored, and it continues until it finds the next instance of 1. So, clearly my understanding of xlNumbers is wrong, I thought it was any number. Is there a format, condition or something I am unaware of?
Thanks in advanced!
Cayne
I've spent a few days now trying to find the answer to the question I am about to pose but I am still missing some that I fear is very simple.
I have two adjacent columns of formula, one will return a row number from another sheet or "", the other will return a low numerical value if there is anything but "" in the adjacent cell. If the adjacent cell is "", this cell will also return "". So I end up with somthing like this:
A---B
23--2
35--1
36--1
72--2
What I would like to have is a bit a vba that will go down column B and if it finds a numerical value, store it in a variable and store the number in column A in a variable. The go to a separate sheet and create a new list with B*instances of A. So the new list would look like:
23
23
35
36
72
72
I believe I know how to do all of it but one thing, and that is hopping between cells containing any number in a column.
This is the code I have been using:
Range("B1:B1000").Find(XlSpecialCellsValue.xlNumbers, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Select
Which works fine for the number 1! Anything over 1 is just rudely ignored, and it continues until it finds the next instance of 1. So, clearly my understanding of xlNumbers is wrong, I thought it was any number. Is there a format, condition or something I am unaware of?
Thanks in advanced!
Cayne
