N
Nic@Rolls-Royce
Hi guys can you help me?
I am struggling with a slow spreadsheet
I use aprox 250 Vlookup formula to drag through information from
sheet to display in a formatted sheet, this makes re-calc a 'cup of te
and cookies' task....as I am now getting fat off cookies, can yo
explain if VBA would be a lot quicker and how I would go about it?
This is the current state of play:-
Sheet Range Called 'QuestionData' is as follows
45012 Apples Green
Round
49018 Oranges Orange Round
85221 Pear Greenish
Oval
and the formatted table is:
Column A Column B etc....
Number Colour
85221 =vlookup(A1,QuestionData,3,FALSE)
45012
(Sorry if the example tables are not clear)
Is there a way (using VBA)
I can say.....
Lookup A1 in 'Formatted Table', search Question Data and Find Vaule
Copy associated column A to 'Formated Table' B
Move to next values in Formatted table and search Question Data an
Find Vaule
Copy associated column A to next row in 'Formated Table' B
keep looping till row (3000)
end Sub
(It would also need to move to the next value if value does not exis
in the table called 'Question Data' as some values although hav
numbers do not have details........yet, as they are added to th
'Question Data' the script would pick them up the next time it wa
run...)
Sorry 'Bout the psuedo, pig latin type dummy code...but hopefully yo
see what I mean???
All the best
Ni
I am struggling with a slow spreadsheet
I use aprox 250 Vlookup formula to drag through information from
sheet to display in a formatted sheet, this makes re-calc a 'cup of te
and cookies' task....as I am now getting fat off cookies, can yo
explain if VBA would be a lot quicker and how I would go about it?
This is the current state of play:-
Sheet Range Called 'QuestionData' is as follows
45012 Apples Green
Round
49018 Oranges Orange Round
85221 Pear Greenish
Oval
and the formatted table is:
Column A Column B etc....
Number Colour
85221 =vlookup(A1,QuestionData,3,FALSE)
45012
(Sorry if the example tables are not clear)
Is there a way (using VBA)
I can say.....
Lookup A1 in 'Formatted Table', search Question Data and Find Vaule
Copy associated column A to 'Formated Table' B
Move to next values in Formatted table and search Question Data an
Find Vaule
Copy associated column A to next row in 'Formated Table' B
keep looping till row (3000)
end Sub
(It would also need to move to the next value if value does not exis
in the table called 'Question Data' as some values although hav
numbers do not have details........yet, as they are added to th
'Question Data' the script would pick them up the next time it wa
run...)
Sorry 'Bout the psuedo, pig latin type dummy code...but hopefully yo
see what I mean???
All the best
Ni