Results 1 to 3 of 3

Thread: VB help needed :(

  1. #1
    Bonnet mounted gunsight megah0's Avatar
    Join Date
    Jul 2003
    Location
    Birmingham
    Posts
    3,381
    Thanks
    79
    Thanked
    73 times in 49 posts

    VB help needed :(

    Code:
    Sub Month()
    Sheets("Sheet2").Select
    Range("J2:J13").Select
    
    'setting the cells interested in
    Months = Range("B2:B12421")
    Years = Range("C2:C12421")
    
    If Years = 1971 And Months = 1 Then
    'setting the equation
    total = 0
    r = 2
     Do
        total = total + Cells(r, 10) / 30
        r = r + 1
        
    Loop Until Cells(r, 7) = "End of data"
    End If
    
        
            
        
        
        
        
    End Sub
    Can anyone spot an error with this?

    The data is from a spreadsheet which records daily water measurements over a period of 11 years, the task is to design a macro to calculate a mean value for each year, month and week.

    line 9 is where a type mismatch error occurs.

    Cheers
    Recycling consultant

  2. #2
    Senior Member
    Join Date
    Jul 2003
    Location
    ZA ✈ UK
    Posts
    622
    Thanks
    0
    Thanked
    0 times in 0 posts
    I haven't much experience with VBA (Visual Basic for Applications, the subset of VB used in Office macros), and I've never worked with it in Excel. That said, a friend of mine (Quite an experienced programmer, but completely foreign to VBA) was having a similar problem a few weeks ago, when I looked this up.

    Short, you want:

    Code:
    Months = Range("B2:B12421").Value
    Years = Range("C2:C12421").Value
    Long, why you want:
    Range, as used above, is a method. It takes a parameter, the range that you want, and returns a Range object which, among other things, gives you access to the value of that particular range (You can also get & set the font, colours, etc.). The reason it's failing on the IF statement is because it's trying to compare a simple number value with a complex Range object - such a comparison is invalid, because the two types (Integer & Range) don't match.

    If you get stuck next time you're in the code editor, press F1. Then use the search feature to look up the documentation on the method you're having issues with. It is rather unintuitive, but everything is there if you dig long enough. Hope you come right.

  3. #3
    Treasure Hunter extraordinaire herulach's Avatar
    Join Date
    Apr 2005
    Location
    Bolton
    Posts
    5,618
    Thanks
    18
    Thanked
    172 times in 159 posts
    • herulach's system
      • Motherboard:
      • MSI Z97 MPower
      • CPU:
      • i7 4790K
      • Memory:
      • 8GB Vengeance LP
      • Storage:
      • 1TB WD Blue + 250GB 840 EVo
      • Graphics card(s):
      • 2* Palit GTX 970 Jetstream
      • PSU:
      • EVGA Supernova G2 850W
      • Case:
      • CM HAF Stacker 935, 2*360 Rad WC Loop w/EK blocks.
      • Operating System:
      • Windows 8.1
      • Monitor(s):
      • Crossover 290HD & LG L1980Q
      • Internet:
      • 120mb Virgin Media
    Is it not slightly excessive to do this using a macro when you could do it in a cell with no problems at all?

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help needed...overclocking hell.
    By RDL in forum Help! Quick Relief From Tech Headaches
    Replies: 1
    Last Post: 11-05-2005, 02:56 PM
  2. USA/Canada ONLY: Free VB .NET 2003 Standard Edition
    By Atomic in forum Retail Therapy and Bargains
    Replies: 2
    Last Post: 31-05-2004, 08:14 PM
  3. Advice needed Re: Virus plz :)
    By Lujan in forum Software
    Replies: 2
    Last Post: 02-03-2004, 02:44 PM
  4. Radeon 8500 linux driver install problems
    By Dorza in forum Software
    Replies: 0
    Last Post: 22-09-2003, 12:00 PM

Posting Permissions

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