Solved

Code loosing focus

Posted on 2013-06-03
5
188 Views
Last Modified: 2013-06-09
In the code below the For statement
     For i = 0 To 2
      dest2.Offset(0, comp(1, i)) = orig_comp.Offset(0, comp(0, i))
    Next i

should still be  seeing the value  from the If statement below but is looking at Sheet4


  If r(1, 1).Offset(0, 4) = 0 Then
    dest.Value = r(1, 1).Value
    Dim c As Integer
    c = r(1, 1).Row


_________________________________________________________________________




Sub DiagnoseData(Optional NoUse As Integer)
Dim r As Range, headr As Range
Dim dest As Range, s As String, i As Integer
Dim total As Range, dest2 As Range
Dim orig_comp As Range
Dim comp(0 To 1, 0 To 10) As Integer
Dim protExc As Boolean, protM3PM As Boolean

ClearSheet "Exceptions", 1
protExc = IsProtected("Exceptions")
protM3PM = IsProtected("Missing 3PM prices")

If protExc Then
  Sheet2.Unprotect Bpwd
End If

If protM3PM Then
  Sheet3.Unprotect Bpwd
End If

Application.Calculation = xlCalculationManual
Set r = Sheet19.Range("A3:D3") ' Exceptions Sheet
Set headr = Sheet19.Range("A2:D2") 'CUSIP tmp sheet
Set dest = Worksheets("Exceptions").Range("A2")
Set dest2 = Sheet3.Range("A2") 'Missing 3 PM prices tab
Set orig_comp = Sheet4.Range("A3") 'Compare sheet line
comp(0, 0) = FindCol("Compare", 2, "Security CUSIP")
comp(0, 1) = FindCol("Compare", 2, "Exchange")
comp(0, 2) = FindCol("Compare", 2, "Security description")
comp(1, 0) = FindCol("Missing 3PM prices", 1, "CUSIP")
comp(1, 1) = FindCol("Missing 3PM prices", 1, "Exchange")
comp(1, 2) = FindCol("Missing 3PM prices", 1, "Security description")
While r(1, 1) <> ""
       
 
  If r(1, 1).Offset(0, 4) = 0 Then 'missing prices
    dest.Value = r(1, 1).Value

    Dim c As Integer
    c = r(1, 1).Row
''''this is where problem is - needs to retain value from the above If statement
            For i = 0 To 2
      dest2.Offset(0, comp(1, i)) = orig_comp.Offset(0, comp(0, i))
    Next i

    Set dest = dest.Offset(1, 0) 'Exceptions
    Set dest2 = dest2.Offset(1, 0) 'missingprices
  End If
  Set orig_comp = orig_comp.Offset(1, 0)
  Set fairval = fairval.Offset(1, 0)
  Set r = r.Offset(1, 0)
Wend
0
Comment
Question by:leezac
  • 3
  • 2
5 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39217137
I'm a little confused.

In the If statement r refers to a range on a sheet with codename Sheet19 and dest refers to a worksheet with the tab name 'Exceptions'.

In the loop dest2 refers to a worksheet with the codename Sheet3 and orig_comp refers to a range on a sheet with codename Sheet4.

So how does the if statemtent refer to Sheet4?

Also, the code in the loop is referring to Sheet4.
0
 

Author Comment

by:leezac
ID: 39217225
I am really confused and not sure if I can give insite.  

Should take items that are on Sheet 19 (Cusip_tmp) column A and insert into Exceptions (that is OK)
then take same value from sheet 19 column A and input to sheet3 column A with values and then add values from Sheet 4 column L and put in column B on sheet3 and column F from sheet 4 and put into Column C on sheet3

somehow the exceptions piece is working fine, but not dest2

   Set dest = dest.Offset(1, 0) 'Exceptions
    Set dest2 = dest2.Offset(1, 0) 'missingprices


Thanks for even looking at.
0
 

Author Comment

by:leezac
ID: 39217378
I am going to keep this open but also post another work around in another post.  If someone can explain to me what this code is doing - that would be helpful.  I have been looking at for a while.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 39217419
Could you attach a sample file?

That might help shed some light on what's going on.

One thing that I can see that could cause problem is using a mix of codenames and tab names.

Also this notation can be confusing.
r(1, 1).Value

Open in new window


If you want to refer to a specific cell in a range it might be clearer to use something like this.
r.Cells(1,1).Value

Open in new window


That might just be me though.:)
0
 

Author Comment

by:leezac
ID: 39219129
No - I was seeing also that might be an issue but did not know what to add exactly.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now