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

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)?


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=545141

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?


--
andysgirl8800
------------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...o&userid=34752
View this thread: http://www.excelforum.com/showthread...hreadid=545141

Refreshingly:

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

--
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 tha
what I'm used to from access. can I also rearrange columns after I'v
deleted the ones I don't need? in other words, if I us
Range("A:A,C:C,E:F,J:K,L:L,O:P").EntireColumn.Dele te, 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
and F

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

--
andysgirl880
-----------------------------------------------------------------------
andysgirl8800's Profile: http://www.excelforum.com/member.php...fo&userid=3475
View this thread: http://www.excelforum.com/showthread.php?threadid=54514

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"
wrote in message
news:andysgirl8800.28d99y_1148565604.3053@excelfor um-nospam.com...

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.Dele te, 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