+ Reply to Thread
Results 1 to 6 of 6

Help with find and replace macro

  1. #1
    davegb
    Guest

    Help with find and replace macro

    Now I'm working on a macro to find and replace column headers in a
    spreadsheet that the raw data I use in XL comes from. I've only got a
    week left on my contract with the state which may be enough time to get
    another set of data out to the counties. (Have applied for a full time
    job here, so I might be back.)
    I have 2 columns of data, starting at B40, in one workbook, call it
    ABC. Column B contains the Source document headers I want to replace in
    the other workbook, and Column C has the headers I want to replace them
    with. I want to select the downloaded data worksheet, then run the
    macro to go the the other workbook, get the old header in cell B40, try
    to find it in row 1 in the downloaded datasheet and replace it with the
    text in C40. Then go to B41, and repeat the procedure until it hits a
    blank cell in column B. If it fails to find the text from column B,
    just continue with the next cell in the column.
    This will make reformatting the incoming datasheets much quicker, so I
    can then copy them into the existing workbook with the new quarter's
    data.
    Can anyone point me in the right direction as to how to set this up? I
    appreciate the help.


  2. #2
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    Try using a VLOOKUP worksheet function it may be quicker and easier than coding a macro.

    You'll have to put the VLOOKUP in the a cell next to the old header and then you can cut and replace the Old Header with the New Header.

    =VLOOKUP(A6,[sourceworkbook.xls]Sheet3!$A$1:$B$25,2,FALSE)

    change the reference in red to the sourceworkbook, sheet and range where the new headers need to be pulled from.

  3. #3
    Rowan
    Guest

    Re: Help with find and replace macro

    Dave

    As suggested above a VLOOKUP will do this for you with ease. You could
    however put the vlookups in a new row below the original headers in which
    case you just need to add one row rather than multiple columns.

    If you want to do it with a macro then it would look something like this
    (assuming the data and headers are in sheet 1 of the respective workbooks):

    Sub Chng_Header()

    Dim foundHead As Range
    Dim oHeads As Range
    Dim cell As Range
    Dim oldHead As String
    Dim newHead As String
    Dim endRow As Long
    Dim Headers As Workbook
    Dim Data As Workbook

    Set Headers = Workbooks("ABC.xls")
    Set Data = Workbooks("Data.xls")

    endRow = Headers.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
    With Headers.Sheets(1)
    Set oHeads = Range(.Cells(40, 2), .Cells(endRow, 2))
    End With

    For Each cell In oHeads
    oldHead = cell.Value
    newHead = cell.Offset(0, 1).Value
    With Data.Worksheets(1).Range("A1:J1") 'Headers to be replaced
    Set foundHead = .Find(oldHead, LookIn:=xlValues)
    End With
    If Not foundHead Is Nothing Then
    foundHead.Value = newHead
    End If
    Next cell

    End Sub

    Regards
    Rowan

    "bhofsetz" wrote:

    >
    > Try using a VLOOKUP worksheet function it may be quicker and easier than
    > coding a macro.
    >
    > You'll have to put the VLOOKUP in the a cell next to the old header and
    > then you can cut and replace the Old Header with the New Header.
    >
    > =VLOOKUP(A6,[sourceworkbook.xls]Sheet3!$A$1:$B$25,2,FALSE)
    >
    > change the reference in red to the sourceworkbook, sheet and range
    > where the new headers need to be pulled from.
    >
    >
    > --
    > bhofsetz
    > ------------------------------------------------------------------------
    > bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
    > View this thread: http://www.excelforum.com/showthread...hreadid=381426
    >
    >


  4. #4
    davegb
    Guest

    Re: Help with find and replace macro



    Rowan wrote:
    > Dave
    >
    > As suggested above a VLOOKUP will do this for you with ease. You could
    > however put the vlookups in a new row below the original headers in which
    > case you just need to add one row rather than multiple columns.
    >
    > If you want to do it with a macro then it would look something like this
    > (assuming the data and headers are in sheet 1 of the respective workbooks):
    >
    > Sub Chng_Header()
    >
    > Dim foundHead As Range
    > Dim oHeads As Range
    > Dim cell As Range
    > Dim oldHead As String
    > Dim newHead As String
    > Dim endRow As Long
    > Dim Headers As Workbook
    > Dim Data As Workbook
    >
    > Set Headers = Workbooks("ABC.xls")
    > Set Data = Workbooks("Data.xls")
    >
    > endRow = Headers.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
    > With Headers.Sheets(1)
    > Set oHeads = Range(.Cells(40, 2), .Cells(endRow, 2))
    > End With
    >
    > For Each cell In oHeads
    > oldHead = cell.Value
    > newHead = cell.Offset(0, 1).Value
    > With Data.Worksheets(1).Range("A1:J1") 'Headers to be replaced
    > Set foundHead = .Find(oldHead, LookIn:=xlValues)
    > End With
    > If Not foundHead Is Nothing Then
    > foundHead.Value = newHead
    > End If
    > Next cell
    >
    > End Sub
    >
    > Regards
    > Rowan
    >


    Thanks Rowan and bhofsetz!
    I prefer to use a macro for a couple of reasons. One, I'm trying to
    learn VBA, I need to know it for this job. 2, most of what I'm doing,
    though not specifically this part, is going to people who are total XL
    novices and need things as totally automated as possible. 3, I'm hoping
    to train someone to do these sheets before I leave, so if I don't get
    the job here, the counties will still be able to get this data in a
    very useful form. That has to be very automated.
    I'm trying to get the macro to run. I've modified it from what you
    sent, Rowan, to make the variable names consistent with my naming
    system. It's not working at the line I understand the least.

    Sub Chng_Header()


    Dim rFoundHd As Range
    Dim rOldHds As Range
    Dim cell As Range
    Dim strOldHd As String
    Dim strNewHd As String
    Dim endRow As Long
    Dim HeadersBk As Workbook
    Dim DataBk As Workbook


    Set HeadersBk = Workbooks("Test Overall Statewide and County
    Percentages.xls")
    Set DataBk = ActiveWorkbook


    endRow = HeadersBk.Sheets(19).Cells(Rows.Count, 2).End(xlUp).Row
    With HeadersBk.Sheets(1)
    Set rOldHds = Range(.Cells(40, 2), .Cells(endRow, 2))
    End With

    rOldHds.Select 'TEMP


    For Each cell In rOldHds
    strOldHd = cell.Value
    strNewHd = cell.Offset(0, 1).Value
    With DataBk.Worksheets(1).Range("A1:P1") 'Headers to be
    replaced
    Set rFoundHd = .Find(strOldHd, LookIn:=xlValues)
    End With
    If Not rFoundHd Is Nothing Then
    rFoundHd.Value = strNewHd
    End If
    Next cell


    End Sub

    The range rOldHds is not anything. Notice I put in, temporarily, the
    rOldHds.Select command to see what it did. I get the error message that
    select method failed. So that range is not being set. And since I have
    no idea what

    Set rOldHds = Range(.Cells(40, 2), .Cells(endRow, 2))

    does, other than set a range, so I can't debug it.
    Can you see what's wrong?
    Thanks for the help.


  5. #5
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481
    Dave,
    You are getting the error message on this line

    rOldHds.Select 'TEMP

    becase the workbook and worksheet on which you are trying to select that range are not the active book and sheet.
    You would either have to manually select that book and sheet or do it in the code with:

    HeadersBk.Sheets(1).Activate
    then
    rOldHds.Select 'TEMP

    I also noticed that you are counting rows on Sheet(19) and then selecting the range on Sheet(1). Is this correct?

  6. #6
    davegb
    Guest

    Re: Help with find and replace macro



    bhofsetz wrote:
    > Dave,
    > You are getting the error message on this line
    >
    > rOldHds.Select 'TEMP
    >
    > becase the workbook and worksheet on which you are trying to select
    > that range are not the active book and sheet.
    > You would either have to manually select that book and sheet or do it
    > in the code with:
    >
    > HeadersBk.Sheets(1).Activate
    > then
    > rOldHds.Select 'TEMP
    >
    > I also noticed that you are counting rows on Sheet(19) and then
    > selecting the range on Sheet(1). Is this correct?
    >
    >

    Thanks! It's working fine now.


+ 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