Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    format cells (excel 2000)

    I received reports from HR personals around the world every month. These reports have same fields but cells are formatted in different way. I need to format these reports individually and save them as

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: format cells (excel 2000)

    Excel can do all this. But if you plan to accumulate all this data, and it will be more than 60,000 records, and you need to process it or analyse it all in the future, Access might be the better data repository. And month-by-month Excel reports won't be convenient if you need to run meta-analyses in either application.

    If you'd like to post a censored (disguised) sample including what you have coded so far, some of the Loungers who deal with multiple-country formatting issues can help in more detail.
    -John ... I float in liquid gardens
    UTC -7±DS

  3. #3
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    In attachment GBA, I have a macro to format the report in a standard way. For example, when I receive a report from Malaysia (attachment MYA), I can open MYA and run marco to format it.

    But for some other formatting, I don

  4. #4
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    Here's MYA attachment

  5. #5
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    Here

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: format cells (excel 2000)

    Could you elaborate on what you are trying to do, I am a little confused about it.

    Is Checking the contents in COl J of particular sheet, against the values in column C of the "Code" sheet of the first workbook enough? The codes are not unique. Or do you have to check in both the sourcecode ([img]/forums/images/smilies/cool.gif[/img] and subarea code© for a match. If so, I suggest creating a column combining these 2 columns in the "Code" sheet so you can use MATCH to speed the searching and reduce the amount of coding required.
    If you combine CODE cols b & c together into D (eg in D2 enter =B2&C2 and copy it down the columns), you can use code like:

    <pre> Dim rng As Range
    Dim rLookup As Range
    Dim x As Long
    Dim rCell As Range
    'Change the names and ranges as appropriate
    Set rLookup = Workbooks("5-399517-GBA.xls").Worksheets("code").Range("d126")
    Set rng = Range(Range("j1"), Range("j65536").End(xlUp))
    'other code
    For Each rCell In rng
    x = 0
    On Error Resume Next
    x = Application.WorksheetFunction. _
    Match(rCell.Offset(0, -1) & rCell, rLookup, 0)
    On Error GoTo 0
    If x = 0 Then rCell.Interior.Color = vbYellow
    Next</pre>


    Concerning deleting the rows starting in A1 if the value in A is not a number try this code in your routine.
    <pre> Dim rng As Range
    Dim x As Long
    'other code
    Set rng = Range(Range("A1"), Range("A65536").End(xlUp))
    For x = rng.Rows.Count To 1 Step -1
    If Not IsNumeric(rng(x)) Then rng(x).EntireRow.Delete
    Next</pre>


    Note: if you use them both, you shouldn't use 2 statements that DIM the same variable name.

    Hope this helps,
    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    If you define a name in the MYA workbook that refers to the code table in the GBA workbook then you can use the name in the conditional format formula to look the code up in the code table. For example, if you define the name CodeTbl in the MYA workbook as ='[GBA.xls]code'!$C$3:$C$26, then you can use the formula =ISERROR(MATCH(J1,CodeTbl,0)) in your conditional formatting.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    Sorry for the confusing. I try to make it clearer.

    What I like to do is
    1.Checking the contents in COL J of particular sheet (like MYA), against the values in column C of the

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: format cells (excel 2000)

    1/2 is done as I suggested in my post
    3,4, 5, 6 are all adaptable from the way 1/2 is done by using match in the appropriate range. In these cases, you should not have to add the 2 columns together you can just match the "rCell" against the appropriate range.

    I formatted the code (which has the side effect that it can not be copied directly into VB).
    Copy the code first to an empty excel sheet, then select the range and copy it from excel and paste it into your code


    Steve

  10. #10
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    Hi Steve, thanks for your advice. But I don

  11. #11
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    Hi Legare, I thought you mean I should define the name CodeTbl in the Code (not MYA) worksheet. MYA is just one of many reports I received. Instead of designing macro individually for all the reports I get, I like to use the Macro worksheet (GBA xls) to repeat the macro. So I select c3-c26 in Code worksheet, go insert-define name as Code Tbl, then I go to Macro worksheet, in J1, I typied the formula =ISERROR(MATCH(J1,CodeTbl,0)), Excel told me there is a circular reference.
    What

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: format cells (excel 2000)

    copy the code from the wopr post into the excel sheet
    copy the code from the excel sheet into VB macro pane into your code.

    Steve

  13. #13
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    Sorry, I know nothing about code. You mean copy code into the new excel sheet, like cell A1 or into VB editor coding area in new excel? I tried both, both said there is an invalid outside procedure.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: format cells (excel 2000)

    Yes copy it into excel sheet A1. (the formatted code I put in, can not be copied and pasted in VB directly, it makes it one long line)
    By putting it into excel it separates into all the lines
    then copy this code and place it within the routine you already have. This is not a standalone code. It is just a "snippet of code" from a procedure. It was an example of code you could add to your current procedure to do additonal things.

    Steve

  15. #15
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: format cells (excel 2000)

    Okey, I think I copied the code into the worksheet. But what's the "invalid outside procedure"?
    Pls check the attachment. Tks.

Page 1 of 2 12 LastLast

Posting Permissions

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