Solved

Code not finding correct column

Posted on 2014-02-21
36
221 Views
Last Modified: 2014-02-26
I did not write this code and am having trouble fixing it.  Using column I and J if column J (10) is null then get value out of column 9 (I) otherwise if Column 10 (J) is not null then the the values from column J (10).



If Sheet1.Cells(row + 1, 10) = "" Then                'if J is null          
       INR = Sheet1.Cells(row, 9)
    ELSE    
            INR = Sheet1.Cells(row, 10)       'If J is not null then get value from J
    End If
0
Comment
Question by:leezac
  • 22
  • 9
  • 5
36 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39878388
Why are you checking the row after the values

If Sheet1.Cells(row + 1, 10) = "" Then
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39878392
See if this works:

If Len(Sheet1.Cells(row, "J")) = 0 Then                'if J is null          
       INR = Sheet1.Cells(row, "I")
Else    
       INR = Sheet1.Cells(row, "J")       'If J is not null then get value from J
End If

Kevin
0
 

Author Comment

by:leezac
ID: 39878406
Well there is more to the code and it looks at adding the value also from another row, but  just thought would simplify in question.  I included a sample, but the code does not work, but you can see the whole code.
test-.zip
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39878417
It's not working because you commented out this line:

TradeCount = Application.CountA(Sheets("Date Input").Range("B3"))

Which depends on the tab "Date Input" being present which is not.

At this point I would say someone has buggered the workbook.

What are you trying to do?

Kevin
0
 

Author Comment

by:leezac
ID: 39878426
Kevin - I tested the code and it still added from column "I" when there were 10 columns.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39878432
That's impossible. Without TradeCount being initialized to anything other than zero - it's default value - the main loop will exit without any iterations. So you have either posted the wrong code or you are doing something that you are not telling us.

I ran the code and it exited without doing anything.

Kevin
0
 
LVL 58

Expert Comment

by:Gary
ID: 39878469
I'm getting a corrupted workbook, VBA is unreadable.
0
 

Author Comment

by:leezac
ID: 39878731
OK - thanks - I will repost more info tomorrow.  In the sample I took code out of the original file.  I will try to fix and resend.  Kevin thanks for advice.  I will look at tradecount line.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39878861
It will also help considerably if you describe what you are expecting the macro to actually do. The code is not very good. Perhaps if you tell us what you are desiring we can whip up a much more clean and reliable macro.

Kevin
0
 

Author Comment

by:leezac
ID: 39879804
Great.

The code takes looks for TradeDate - you know that.
It looks for the Fund:(in this case it is 288- test-test2), but there will be others also.  Just using one for an example.  
Basically I need the following:
1. The code needs to loop through and finds the values for to place on the Summary tab in column D - these are highlighted in Yellow in the attached file.
Note:  the values will be in Column J or I
  Code references INR


2. I need amounts from the last column highlighted in Green need to be copied to Column E of the Summary tab.  Code references USD

Fund number will go in column A
text after number will go in column B
Text after second hypen for Fund number in Column C
INR amount in column D
USD amount in column E

Note: Currently if the text after the Fund number has a second hyphen the current code is placing that text in Column C.  
Example for fund number and text
Fund: 288-test-test

Note I do not need amounts where there is an "R" in the cell to the Right of an amount.


The current code is not working with the values are in column J not I.  It is showing the wrong value highlighted in Red on the Summary tab for most of the values (not all).

Basically I am getting values from one sheet and placing on another, but the problem is that the values are in different columns.  

Thanks in advance - I can use the help to get this done.
test-.zip
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39879862
Please post a workbook that has the worksheet "Date Input". The code won't run without it.

Kevin
0
 

Author Comment

by:leezac
ID: 39879879
Attached has the Date input tab
test---2-.zip
0
 
LVL 58

Expert Comment

by:Gary
ID: 39879994
Code makes no sense and your explanation even less so.

What are you trying to achieve here?
TradeCount = Application.CountA(Sheets("Date Input").Range("B3"))

What is Start supposed to be?
Trade = Start.Cells(TradeRow + 22, 2)

If you are going to name things to make it easier to reference them then at least use a name that means something, e.g. ColumnCount makes no sense when it refers to rows.
0
 

Author Comment

by:leezac
ID: 39880067
Cathal - I did not write the code.  
I tried to explain the best I could.  I am trying to fix the issue so people can use the file.  

I don't know why the Trade=Start.Cells code is there or why the code needs to reference the date other than to make sure the date is current -  I can add that code later.  I just need help

to get the numbers in Green and Yellow to the correct columns in the summary sheet.  There will always be a variation of 9 to 10 columns on the Application tab.  

I just need the fund number in one column on the summary, the text that is associated to the fund number in another column and the green values in one column and the yellow in the other.  

The person that wrote the code used "Work" to locate the correct rows.  

It is unfortunate that the data coming in is so messy, but that is the way it is.  It is my job to help get it to filter the values out that are needed to the Summary tab.

Thank you for looking at the code.  If I can answer any other questions, please let me know.
Leezaca
0
 
LVL 58

Expert Comment

by:Gary
ID: 39880118
Try this, it's the best I can gather from what you have said.

...
ColumnCount = 65536 - Application.WorksheetFunction.CountBlank(RealizeRaw.Range(Selection, Selection)) + 5

temprows = 1

For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
    fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then

        If RealizeRaw.Cells(Row + 1, "J") <> 0 And RealizeRaw.Cells(Row + 1, "J") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
            INR = RealizeRaw.Cells(Row + 1, "J")
            USD = RealizeRaw.Cells(Row, "J")
        
        ElseIf RealizeRaw.Cells(Row + 1, "I") <> 0 And RealizeRaw.Cells(Row + 1, "I") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
            INR = RealizeRaw.Cells(Row + 1, "I")
            USD = RealizeRaw.Cells(Row, "I")
        
        End If
        RealizeSum.Cells(temprows, "A") = fund_id(0)
        RealizeSum.Cells(temprows, "B") = fund_id(1)
        RealizeSum.Cells(temprows, "C") = fund_id(2)
        RealizeSum.Cells(temprows, "D") = INR
        RealizeSum.Cells(temprows, "E") = USD
        temprows = temprows + 1

End If

Next

'Sort and Seperate Fund Numbers
RealizeSum.Activate
...

Open in new window

0
 

Author Comment

by:leezac
ID: 39880121
Ok - I will look at in the morning.  Thanks
0
 

Author Comment

by:leezac
ID: 39880137
Ok = looking at tonight

I am getting an error on this line

ColumnCount = 65536 - Application.WorksheetFunction.CountBlank(RealizeRaw.Range(Selection

I am using Excel 2010

The error is 'Range' of object _ Worksheet failed


I just wanted to add that the color coding is something I added to show what values were the correct values to copy.



This how I have the code added.

Private Sub Formatrealize()
Dim RowCount As Integer
Dim Fund As String
Dim Trade As String
Dim TradeRow As Integer
Dim TradeCount As Integer
Dim INR As String
Dim USD As String
Dim List As Integer
Dim ColumnCount As Integer
Call DefineGlobals
'Clear contents before filter
RealizeSum.Select
Cells.ClearContents


ColumnCount = 65536 - Application.WorksheetFunction.CountBlank(RealizeRaw.Range(Selection, Selection)) + 5

temprows = 1

For row = 1 To ColumnCount
If RealizeRaw.Cells(row, 1) = "Fund:" Then
    fund_id = Split(RealizeRaw.Cells(row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(row, 1)) Then

        If RealizeRaw.Cells(row + 1, "J") <> 0 And RealizeRaw.Cells(row + 1, "J") <> "" And RealizeRaw.Cells(row, "J") <> "R " Then
            INR = RealizeRaw.Cells(row + 1, "J")
            USD = RealizeRaw.Cells(row, "J")
       
        ElseIf RealizeRaw.Cells(row + 1, "I") <> 0 And RealizeRaw.Cells(row + 1, "I") <> "" And RealizeRaw.Cells(row, "J") <> "R " Then
            INR = RealizeRaw.Cells(row + 1, "I")
            USD = RealizeRaw.Cells(row, "I")
       
        End If
        RealizeSum.Cells(temprows, "A") = fund_id(0)
        RealizeSum.Cells(temprows, "B") = fund_id(1)
        RealizeSum.Cells(temprows, "C") = fund_id(2)
        RealizeSum.Cells(temprows, "D") = INR
        RealizeSum.Cells(temprows, "E") = USD
        temprows = temprows + 1

End If

Next

'Sort and Seperate Fund Numbers
RealizeSum.Activate


End Sub
0
 

Author Comment

by:leezac
ID: 39880150
Cathal

I changed the row with the error to this

ColumnCount = RealizeRaw.Range("A1").Offset(RealizeRaw.Rows.Count - 1, 0).End(xlUp).row

and it looks to be working.  I will continue to test and post again tomorrow.

Thanks for help.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:leezac
ID: 39880151
Cathal

Question

Why did you put this at the end??


'Sort and Seperate Fund Numbers
RealizeSum.Activate
0
 

Author Comment

by:leezac
ID: 39880157
Ok. I am attaching the test file using Cathal's code.  It seems to be finding the correct amounts for the different columns, but it is duplicating amuonts and not importing all the amounts in yellow (looks like the ones that are negative).  

For every Date that should be a an amount even if it is "0".  Do not need the amounts with "R" in the column to the right of the amount, but do need amount below and that amount will go into column D of the Summary.  

The original code looked for the word "Work" to help find the amounts.  I am open to any way possible.  

Note - can step through code.  Can't use button on Date Input - has error

Again - thanks for help.
test-.zip
0
 

Author Comment

by:leezac
ID: 39880160
Cathal,  It looks like the problem is when there is an "R" the amounts duplicate from the line before where the rows where there is an "R"
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 39880668
Ok try this
temprows = 1

For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
    fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then

        If RealizeRaw.Cells(Row + 1, "J") <> 0 And RealizeRaw.Cells(Row + 1, "J") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
            INR = RealizeRaw.Cells(Row + 1, "J")
            USD = RealizeRaw.Cells(Row, "J")
        
        ElseIf RealizeRaw.Cells(Row + 1, "I") <> 0 And RealizeRaw.Cells(Row + 1, "I") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
            INR = RealizeRaw.Cells(Row + 1, "I")
            USD = RealizeRaw.Cells(Row, "I")
        
        End If
        If INR <> "" Or USD <> "" Then
        RealizeSum.Cells(temprows, "A") = fund_id(0)
        RealizeSum.Cells(temprows, "B") = fund_id(1)
        RealizeSum.Cells(temprows, "C") = fund_id(2)
        RealizeSum.Cells(temprows, "D") = INR
        RealizeSum.Cells(temprows, "E") = USD
        temprows = temprows + 1
        End If
        INR = ""
        USD = ""
End If
Next

Open in new window

0
 

Author Comment

by:leezac
ID: 39880760
Great Work!!!!  It is working now to get both columns and the correct amounts and is not duplicating.  I need to check tomorrow to verify the amounts with the user.  I really appreciate your attention to this and great response.  Thank you!!  That helps so much.

Well I went back and looked - there is one value it did not pull.  

There would be a "0" in E and the value in red in column D.  I highligted the value in RED in the attached file.   Not sure how will capture those values.  The original code used "work" as a guidepost somewhat to find values.  Again, though it was not working and yours is.

I went ahead and ran and included the "R"'s until I get more information tomorrow.
test-.zip
0
 

Author Comment

by:leezac
ID: 39880769
I am not sure how notifications work.  i edited the last comment.  Have one value not pulling in.  Attached file in previous comment.
0
 

Author Comment

by:leezac
ID: 39880771
If I leave the code in for the R values it does not pull anything in.

   When I took the code out for "R" values the code does bring in all values appropriately except for the one I highligthed in RED in the last attachement.
0
 

Author Comment

by:leezac
ID: 39880776
I edited my last post -
0
 
LVL 58

Expert Comment

by:Gary
ID: 39880777
If you edit a question there is no new notification.
temprows = 1

For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
    fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then

        If RealizeRaw.Cells(Row + 1, "J") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
            INR = RealizeRaw.Cells(Row + 1, "J")
            USD = RealizeRaw.Cells(Row, "J")
        
        ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
            INR = RealizeRaw.Cells(Row + 1, "I")
            USD = RealizeRaw.Cells(Row, "I")
        
        End If
        If INR <> "" Or USD <> "" Then
        RealizeSum.Cells(temprows, "A") = fund_id(0)
        RealizeSum.Cells(temprows, "B") = fund_id(1)
        RealizeSum.Cells(temprows, "C") = fund_id(2)
        RealizeSum.Cells(temprows, "D") = INR
        RealizeSum.Cells(temprows, "E") = USD
        temprows = temprows + 1
        End If
        INR = ""
        USD = ""
End If
Next

Open in new window

0
 

Author Comment

by:leezac
ID: 39880782
OK - I went back and looked at the original file.  When a value had an "R" in the cell to the right.  The value was not added to the Summary tab, but and "R" was inserted instead.  So I do not need the "R" values as originally stated

BUT I DO need the values on the row below them for the date.  

Each date will have two values except when there is a value with an "R" then only one value in
column "D" for that date.
0
 
LVL 58

Expert Comment

by:Gary
ID: 39880794
temprows = 1

For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
    fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then

        If RealizeRaw.Cells(Row + 1, "J") <> "" Then
            INR = RealizeRaw.Cells(Row + 1, "J")
            USD = RealizeRaw.Cells(Row, "J")
            If RealizeRaw.Cells(Row, "J") = "R " Then INR = ""
        
        ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" Then
            INR = RealizeRaw.Cells(Row + 1, "I")
            USD = RealizeRaw.Cells(Row, "I")
            If RealizeRaw.Cells(Row, "J") = "R " Then INR = ""
        
        End If
        If INR <> "" Or USD <> "" Then
        RealizeSum.Cells(temprows, "A") = fund_id(0)
        RealizeSum.Cells(temprows, "B") = fund_id(1)
        RealizeSum.Cells(temprows, "C") = fund_id(2)
        RealizeSum.Cells(temprows, "D") = INR
        RealizeSum.Cells(temprows, "E") = USD
        temprows = temprows + 1
        End If
        INR = ""
        USD = ""
End If
Next

Open in new window

0
 

Author Comment

by:leezac
ID: 39881064
I am about to test new code.  I think the developer who wrote this meant to confuse someone trying to fix it.
0
 

Author Comment

by:leezac
ID: 39881088
Ok , I highlighted in red on the Summary tab - there areI duplicates and the negative numbers are not the ones we need.  I thought I explained that earlier.  

I highlited in red on the Application tab the values I do not want in red.  The values I need for column D are in Green.  

The amount that was not updating from the last test is showing correctly now.


Again, I do not need the values in red and are negative to show on the summary tab, but do need the ones in green to go into column D.  The other numbers seem to be showing correctly.

I have attached another file.
test-.zip
0
 
LVL 58

Expert Comment

by:Gary
ID: 39881105
temprows = 1

For Row = 1 To ColumnCount
    If RealizeRaw.Cells(Row, 1) = "Fund:" Then
        fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
    End If
    If IsDate(RealizeRaw.Cells(Row, 1)) Then
        INR = ""
        USD = ""
        If RealizeRaw.Cells(Row + 1, "J") <> "" Then
            INR = RealizeRaw.Cells(Row + 1, "J")
            USD = RealizeRaw.Cells(Row, "J")
        ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" Then
            INR = RealizeRaw.Cells(Row + 1, "I")
            USD = RealizeRaw.Cells(Row, "I")
        End If
        If RealizeRaw.Cells(Row, "J") = "R " Then USD = ""
        If INR <> "" Or USD <> "" Then
            RealizeSum.Cells(temprows, "A") = fund_id(0)
            RealizeSum.Cells(temprows, "B") = fund_id(1)
            RealizeSum.Cells(temprows, "C") = fund_id(2)
            RealizeSum.Cells(temprows, "D") = INR
            RealizeSum.Cells(temprows, "E") = USD
            temprows = temprows + 1
        End If
    End If
Next

Open in new window

0
 

Author Comment

by:leezac
ID: 39881280
Thank you - I need to do more testing tomorrow at work.
0
 

Author Comment

by:leezac
ID: 39887315
OK -- for the last code you posted - we tested and works fine except that help modifying it to only pull in the values that are not "R's" where the date = the date on the Date Input sheet.

I am new with this code and just learned this or I would not of asked to have it pull everything in.  

So we only need the rows where the date in Column A = B3 on the Date Input tab
and show the value on the Summary tab if it is in column I or J

Set Dates = WBMain.Sheets("Date Input")  would be the tab where the date is entered.
0
 
LVL 58

Expert Comment

by:Gary
ID: 39887363
For Row = 1 To ColumnCount
    If RealizeRaw.Cells(Row, 1) = "Fund:" Then
        fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
    End If
    If IsDate(RealizeRaw.Cells(Row, 1)) And Cells(Row, 1) = Sheets("Date Input").Cells(3, "B") Then
        INR = ""
        USD = ""
        If RealizeRaw.Cells(Row + 1, "J") <> "" Then
            INR = RealizeRaw.Cells(Row + 1, "J")
            USD = RealizeRaw.Cells(Row, "J")
        ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" Then
            INR = RealizeRaw.Cells(Row + 1, "I")
            USD = RealizeRaw.Cells(Row, "I")
        End If
        If RealizeRaw.Cells(Row, "J") = "R " Then USD = ""
        If INR <> "" Or USD <> "" Then
            RealizeSum.Cells(temprows, "A") = fund_id(0)
            RealizeSum.Cells(temprows, "B") = fund_id(1)
            RealizeSum.Cells(temprows, "C") = fund_id(2)
            RealizeSum.Cells(temprows, "D") = INR
            RealizeSum.Cells(temprows, "E") = USD
            temprows = temprows + 1
        End If
    End If
Next

Open in new window

0
 

Author Closing Comment

by:leezac
ID: 39890359
Excellant job in staying in touch and help with finding solution.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

19 Experts available now in Live!

Get 1:1 Help Now