+ Reply to Thread
Results 1 to 8 of 8

multi-function macro possibilities?

  1. #1
    Registered User
    Join Date
    05-24-2006
    Posts
    17

    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

  2. #2
    Tom Ogilvy
    Guest

    RE: multi-function macro possibilities?

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


  3. #3
    Registered User
    Join Date
    05-24-2006
    Posts
    17
    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)?

  4. #4
    Tom Ogilvy
    Guest

    Re: multi-function macro possibilities?

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


  5. #5
    Registered User
    Join Date
    05-24-2006
    Posts
    17
    So, if I wanted to delete columns A,C,E,F,J,K,L,O and P, how does that look in code?

  6. #6
    Tom Ogilvy
    Guest

    Re: multi-function macro possibilities?

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


  7. #7
    Registered User
    Join Date
    05-24-2006
    Posts
    17
    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.

  8. #8
    Tom Ogilvy
    Guest

    Re: multi-function macro possibilities?

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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1