Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Code not finding correct column

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
leezac
Asked:
leezac
  • 22
  • 9
  • 5
1 Solution
 
GaryCommented:
Why are you checking the row after the values

If Sheet1.Cells(row + 1, 10) = "" Then
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
leezacAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
zorvek (Kevin Jones)ConsultantCommented:
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
 
leezacAuthor Commented:
Kevin - I tested the code and it still added from column "I" when there were 10 columns.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
GaryCommented:
I'm getting a corrupted workbook, VBA is unreadable.
0
 
leezacAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
leezacAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Please post a workbook that has the worksheet "Date Input". The code won't run without it.

Kevin
0
 
leezacAuthor Commented:
Attached has the Date input tab
test---2-.zip
0
 
GaryCommented:
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
 
leezacAuthor Commented:
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
 
GaryCommented:
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
 
leezacAuthor Commented:
Ok - I will look at in the morning.  Thanks
0
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
Cathal

Question

Why did you put this at the end??


'Sort and Seperate Fund Numbers
RealizeSum.Activate
0
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
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
 
GaryCommented:
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
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
I am not sure how notifications work.  i edited the last comment.  Have one value not pulling in.  Attached file in previous comment.
0
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
I edited my last post -
0
 
GaryCommented:
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
 
leezacAuthor Commented:
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
 
GaryCommented:
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
 
leezacAuthor Commented:
I am about to test new code.  I think the developer who wrote this meant to confuse someone trying to fix it.
0
 
leezacAuthor Commented:
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
 
GaryCommented:
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
 
leezacAuthor Commented:
Thank you - I need to do more testing tomorrow at work.
0
 
leezacAuthor Commented:
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
 
GaryCommented:
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
 
leezacAuthor Commented:
Excellant job in staying in touch and help with finding solution.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 22
  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now