Solved

Adding to code

Posted on 2011-02-25
5
186 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 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