Solved

Adding to code

Posted on 2011-02-25
5
189 Views
Last Modified: 2012-05-11
Hi,

I have the below piece of code and i want to start adding more named ranges like "Today" and "Yesterday"

How can i add on to that code

e.g. my next named ranges would be "MeritToday" & "Merit Yesterday"

Thanks
Seamus
Option Explicit

Public Sub CompareData()
  'compares Today and Yesterday named ranges
  Dim rngToday As Range
  Dim rngYesterday As Range
  Dim rngTcell As Range
  Dim boolFirstTime As Boolean
  
  Set rngToday = ActiveSheet.Range("Today")
  Set rngYesterday = ActiveSheet.Range("Yesterday")
  
  If (rngToday.Columns.Count = rngYesterday.Columns.Count) _
    And (rngToday.Rows.Count = rngYesterday.Rows.Count) Then
  Else
    MsgBox "Sophis Fiscal are Identical!", vbCritical, "Range size difference"
    Exit Sub
  End If
  
  
  boolFirstTime = True
  For Each rngTcell In rngToday
    If rngTcell.Value <> rngYesterday.Cells(rngTcell.Row - rngToday.Row + 1, rngTcell.Column - rngToday.Column + 1).Value Then
    Else
      If boolFirstTime Then
        boolFirstTime = False   'skip header cell
      Else
        Exit Sub
      End If
    End If
  Next
  MsgBox "Data Identical", vbCritical, "Range Data Identical"
End Sub

Open in new window

0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34981667
Seamus2626,

Try the code below to add the range names. Change the ranges to which each of them refer to suit your requirements.

Patrick
ActiveWorkbook.Names.Add Name:="MeritToday", RefersToR1C1:="=Sheet1!R1C1:R10C1"
    Range("B1:B10").Select
    ActiveWorkbook.Names.Add Name:="Merit Yesterday", RefersToR1C1:="=Sheet1!R1C2:R10C2"

Open in new window

0
 

Author Comment

by:Seamus2626
ID: 34981715
Hi Patrick, is not possible for me to simply name a range like the above code as opposed to having to reference each area?

Thanks
Seamus
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 34983088
>is not possible for me to simply name a range like the above code as opposed to having to reference each area?

I don't understand the point you are making. Do please explain. The code  I gave can and will work.
0
 

Author Closing Comment

by:Seamus2626
ID: 34996155
Thanks Patrick

Seamus
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34996205
Seamus - Thanks for the grade - Patrick
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question