multi-function macro possibilities?

Hi all!
I'm new to this forum, but somewhat familiar with VBA in MSAccess97. What I am hoping to accomplish is to create a macro, to be run with a button click that will perform the following functions:

1) Delete some unecessary columns from one spreadsheet that will always have the same column headers in the same format, but updated every day
2) Delete specific data from one column, i.e., fields contain a number like 415444459-01, I want to delete the -01 part and leave the rest of the number
3) Export the newly formated data, minus header row, into an existing table in MSAccess97 (which I may have to do via append query within Access)

I'm really hoping for some suggestions and/or direction on how to begin coding this macro. I've been browsing this forum for a few hours now, and looking through some code, but can't seem to figure out how to program correctly to do the things I need. Any help would be tremendously appreciated!

Thanks

Dim v as Variant, res as Variant
Dim i as long, rng as Range, cell as Range

v = Array("Header2","Header7","Header9")
for i = lbound(v) to ubound(v)
set rng = Rows(1).Cells
res = Application.Match(v(i),rng,0)
if not iserror(res) then
columns(res).Delete
end if
Next
res = Application.MAtch("Header3"),Rows(1),0)
if not iserror(res) then
set rng = Range(cells(2,res),Cells(rows.count,res).End(xlup))
for each cell in res
cell.Value = Left(cell,Len(cell)-3)
Next
End if


If the end is always "-01" then turn on the macro recorder, select the
column, and do Edit=>Replace
What: -01
With: <leave blank>

then turn off the macro recorder and adapt the recorded code to your base
code.

--
Regards,
Tom Ogilvy



"andysgirl8800" wrote:

>
> Hi all!
> I'm new to this forum, but somewhat familiar with VBA in MSAccess97.
> What I am hoping to accomplish is to create a macro, to be run with a
> button click that will perform the following functions:
>
> 1) Delete some unecessary columns from one spreadsheet that will always
> have the same column headers in the same format, but updated every day
> 2) Delete specific data from one column, i.e., fields contain a number
> like 415444459-01, I want to delete the -01 part and leave the rest of
> the number
> 3) Export the newly formated data, minus header row, into an existing
> table in MSAccess97 (which I may have to do via append query within
> Access)
>
> I'm really hoping for some suggestions and/or direction on how to begin
> coding this macro. I've been browsing this forum for a few hours now,
> and looking through some code, but can't seem to figure out how to
> program correctly to do the things I need. Any help would be
> tremendously appreciated!
>
> Thanks
>
>
> --
> andysgirl8800
> ------------------------------------------------------------------------
> andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
> View this thread: http://www.excelforum.com/showthread...hreadid=545141
>
>

thank you so much for your reply! I'll start plugging in the code.
v = Array("Header2","Header7","Header9") should I replace the "Header2" with the actual name of the headers, or is this a range of where the header is in the spreadsheet?
also, what is the (res)?

It was dummy names used as examples. You should put in the actual header
labels that will be searching for (this assumes that they could be in
different locations - if not, you can hard code the locations and delete
coming from the right).

Match is a worksheet function that gives 1-based offset into the range being
searched.

=Match(1,Range("A1:A10"),0)
would return 5 if the value 1 is first found in cell A5.

=Match(1,Range("A1:J10"),0)
would return 5 if the value 1 is first found in cell E1

If the value 1 is not found, it displays/returns #N/A an error value.

You can use this function in VBA with

dim rng as Range, rng1 as Range
dim res as Variant ' since it could hold a number or an error value
' I use res as short for result.
set rng = Range("A1:A10")
res = Application.match(1,rng,0)
' see if 1 was not found
if iserror(res) then
msgbox "Target was not found"
else
set rng1 = rng(res)
msgbox "target was found in cell " & rng1.Address
end if

for the column Range

dim rng as Range, rng1 as Range
dim res as Variant ' since it could hold a number or an error value
' I use res as short for result.
set rng = Range("A1:J10")
res = Application.match(1,rng,0)
' see if 1 was not found
if iserror(res) then
msgbox "Target was not found"
else
set rng1 = rng(1,res)
msgbox "target was found in cell " & rng1.Address
end if

--
regards,
Tom Ogilvy


"andysgirl8800" wrote:

>
> thank you so much for your reply! I'll start plugging in the code.
> v = Array("Header2","Header7","Header9") should I replace the "Header2"
> with the actual name of the headers, or is this a range of where the
> header is in the spreadsheet?
> also, what is the (res)?
>
>
> --
> andysgirl8800
> ------------------------------------------------------------------------
> andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
> View this thread: http://www.excelforum.com/showthread...hreadid=545141
>
>

So, if I wanted to delete columns A,C,E,F,J,K,L,O and P, how does that look in code?

Refreshingly:

Range("A:A,C:C,E:F,J:K,L:L,O:P").EntireColumn.Delete

--
Regards,
Tom Ogilvy


"andysgirl8800" wrote:

>
> So, if I wanted to delete columns A,C,E,F,J,K,L,O and P, how does that
> look in code?
>
>
> --
> andysgirl8800
> ------------------------------------------------------------------------
> andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
> View this thread: http://www.excelforum.com/showthread...hreadid=545141
>
>

That looks easy enough! but the overall coding seems different than what I'm used to from access. can I also rearrange columns after I've deleted the ones I don't need? in other words, if I use Range("A:A,C:C,E:F,J:K,L:L,O:P").EntireColumn.Delete, how do I then:
1) delete rows one and 2, moving all data below row one up.
2) arrange columns by header names in the order of:
"Receipt Date" as column A
"Member Number- Person Code" as column B
"Auth Number" as column C
"Brand Name" as column D
"Plan Status" as column E
"Fill Count" as column F
3) Insert a new column between columns A and B and between columns E and F

To date, all of this has been done manually every day. I would like to automate as much of this process as possible. I thought the code would be like it is for access, but reading through the code, it doesn't make much sense to me.

Rows(1).Resize(2).Delete

Turn the macro recorder and do one move manually.

Turn off the macro recorder and look at the code.

Should give you some insights.

--
Regards,
Tom Ogilvy

"andysgirl8800" <[email protected]>
wrote in message
news:[email protected]...
>
> That looks easy enough! but the overall coding seems different than
> what I'm used to from access. can I also rearrange columns after I've
> deleted the ones I don't need? in other words, if I use
> Range("A:A,C:C,E:F,J:K,L:L,O:P").EntireColumn.Delete, how do I then:
> 1) delete rows one and 2, moving all data below row one up.
> 2) arrange columns by header names in the order of:
> "Receipt Date" as column A
> "Member Number- Person Code" as column B
> "Auth Number" as column C
> "Brand Name" as column D
> "Plan Status" as column E
> "Fill Count" as column F
> 3) Insert a new column between columns A and B and between columns E
> and F
>
> To date, all of this has been done manually every day. I would like to
> automate as much of this process as possible. I thought the code would
> be like it is for access, but reading through the code, it doesn't make
> much sense to me.
>
>
> --
> andysgirl8800
> ------------------------------------------------------------------------
> andysgirl8800's Profile:
http://www.excelforum.com/member.php...o&userid=34752
> View this thread: http://www.excelforum.com/showthread...hreadid=545141
>