Hello and Help!

I have a 3 workbooks with 6 worksheets in each. Each worksheet
contains about 20-25000 rows of information! These workbooks need to
be updated monthly. If you do the math, that's a lot to sort through
and update. It use to take almost a month to update everything for the
last month. I have been able to take it down to a little under 2
weeks. Here's a sample:

STYLE DESCRIPTION FEB MAR APR MAY JUN JUL TOTAL
XXXXXXXX
ABC123 SALES ACTUAL 0 0 0 0 0
SALES PROJ 0 0 0 0 0
SALES PROJ 0 0 0 0 0
SALES LY 0 0 0 0 0
Chg Act/LY 0 0 0 0 0
BOM ACTUAL 0 0 0 0 0
BOM LY 0 0 0 0 0
Chg Act/LY 0 0 0 0 0
PROJ REC DIV 0 0 0 0 0
PROJ REC FMG 0 0 0 0 0
DIFFERENCE 0 0 0 0 0
ORDERED 0 0 0 0 0
SHIPPED 0 0 0 0 0
% FILL 0 0 0 0 0
ORD/PROJ 0 0 0 0 0
VEND PROJ REC 0 0 0 0 0
VEND BOM ONH 0 0 0 0 0
VEND ACT REC 0 0 0 0 0
PROJ REC/ACT REC 0 0 0 0 0

Some of the information is calculated, the rest is entry off vlookups
of different pivot tables.

What I would like is a VBA that would allow me to copy the vlookup by
row in the correct column I need and then change those vlookups in to
value.
For example: if I need the VEND ACT REC for July, I would like to have
a box that would allow me to specify that for all rows that have VEND
ACT REC to copy and paste a particular forumula in the column of my
choosing. I guess I would need a box that would ask me what I am
looking for by row and column. I guess the next line in the box would
ask me what formula I would like to copy and paste into the rows. The
next box would ask me which column do I need to start the copy and
paste in (because the rows would be any that have VEND ACT REC or any
variable that I desire). I would like to have a box that I can check
off if I want to change the forumlas to values after done.

I also need the formula to correctly reference the correct cell in the
vlookup. If the vlookup in the example will be using $A3 as the value
to lookup, when the formulas are copied and pasted, the next cell to
reference in the example would be $A24 (consider a line skipped).

I hope someone can help me.
Thanks
Shawn

You can prompt for cell locations using

Dim rng as Range
On error resume next
set rng = Application.Inputbox( Type:=8)
On Error goto 0
if not rng is nothing then
msgbox "you selected " & rng.address
End if

as an example.

copying formulas follows the same adjustment rules as doing it manually
(relative and absolute references). If those don't work for you, then you
would have to develop the rules and implement them on how to adjust the
formulas.

--
Regards,
Tom Ogilvy


"Shawn" wrote in message
om...
Hello and Help!

I have a 3 workbooks with 6 worksheets in each. Each worksheet
contains about 20-25000 rows of information! These workbooks need to
be updated monthly. If you do the math, that's a lot to sort through
and update. It use to take almost a month to update everything for the
last month. I have been able to take it down to a little under 2
weeks. Here's a sample:

STYLE DESCRIPTION FEB MAR APR MAY JUN JUL TOTAL
XXXXXXXX
ABC123 SALES ACTUAL 0 0 0 0 0
SALES PROJ 0 0 0 0 0
SALES PROJ 0 0 0 0 0
SALES LY 0 0 0 0 0
Chg Act/LY 0 0 0 0 0
BOM ACTUAL 0 0 0 0 0
BOM LY 0 0 0 0 0
Chg Act/LY 0 0 0 0 0
PROJ REC DIV 0 0 0 0 0
PROJ REC FMG 0 0 0 0 0
DIFFERENCE 0 0 0 0 0
ORDERED 0 0 0 0 0
SHIPPED 0 0 0 0 0
% FILL 0 0 0 0 0
ORD/PROJ 0 0 0 0 0
VEND PROJ REC 0 0 0 0 0
VEND BOM ONH 0 0 0 0 0
VEND ACT REC 0 0 0 0 0
PROJ REC/ACT REC 0 0 0 0 0

Some of the information is calculated, the rest is entry off vlookups
of different pivot tables.

What I would like is a VBA that would allow me to copy the vlookup by
row in the correct column I need and then change those vlookups in to
value.
For example: if I need the VEND ACT REC for July, I would like to have
a box that would allow me to specify that for all rows that have VEND
ACT REC to copy and paste a particular forumula in the column of my
choosing. I guess I would need a box that would ask me what I am
looking for by row and column. I guess the next line in the box would
ask me what formula I would like to copy and paste into the rows. The
next box would ask me which column do I need to start the copy and
paste in (because the rows would be any that have VEND ACT REC or any
variable that I desire). I would like to have a box that I can check
off if I want to change the forumlas to values after done.

I also need the formula to correctly reference the correct cell in the
vlookup. If the vlookup in the example will be using $A3 as the value
to lookup, when the formulas are copied and pasted, the next cell to
reference in the example would be $A24 (consider a line skipped).

I hope someone can help me.
Thanks
Shawn

Thanks Tom,

I'm still in the process of learning how to right application. I will
try to figure this all out.

Thanks again for the start. It can only get better from this point.

I have just one question: In the Application.Inputbox, you typed"(
Type:=8)", is it written exactly as that or just "type=8". And is
setting the type of input box I have?

Thanks
Shawn


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Arguments are set using colon equal

set rng = Application.InputBox(Prompt:="Select a range with the mouse", _
Type:=8)

put this in a module, then highlight Application.InputBox and hit F1 to get
help on the function. This should show you what other agruments you have
available. It also will explain what the type argument does.

Note that Application.Inputbox gives you the Excel inputbox which is
different from the VBA inputbox.

ans = InputBox(Prompt:="Type your name")

is different from

ans = Application.InputBox(Prompt:="Type your name", Type:=2)

although the differences may be subtle for many uses. The
application.Inputbox actually acts as a virtual cell, so you could type in a
formula same as in a cell and it would be evaluated. so for the example,
where a string is expected, I could type in

="ABC " & G9

ans would hold the concatenation of the string "ABC " and whatever is in
cell G9 of the active sheet.

--
Regards,
Tom Ogilvy



"Shawn Hobbs" wrote in message
...

Thanks Tom,

I'm still in the process of learning how to right application. I will
try to figure this all out.

Thanks again for the start. It can only get better from this point.

I have just one question: In the Application.Inputbox, you typed"(
Type:=8)", is it written exactly as that or just "type=8". And is
setting the type of input box I have?

Thanks
Shawng


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks a million.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!