Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Code not finding correct column

Posted on 2014-02-21
Medium Priority
227 Views
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
Question by:leezac
[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
• 22
• 9
• 5

LVL 58

Expert Comment

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

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

LVL 81

Expert Comment

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

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

ID: 39878417
It's not working because you commented out this line:

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

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

LVL 81

Expert Comment

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

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

Author Comment

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

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

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

ID: 39879862
Please post a workbook that has the worksheet "Date Input". The code won't run without it.

Kevin
0

Author Comment

ID: 39879879
Attached has the Date input tab
test---2-.zip
0

LVL 58

Expert Comment

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

What are you trying to achieve here?

What is Start supposed to be?

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

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

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
...
``````
0

Author Comment

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

Author Comment

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 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

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

Author Comment

ID: 39880151
Cathal

Question

Why did you put this at the end??

'Sort and Seperate Fund Numbers
RealizeSum.Activate
0

Author Comment

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

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

Gary earned 2000 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
``````
0

Author Comment

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.

test-.zip
0

Author Comment

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

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

ID: 39880776
I edited my last post -
0

LVL 58

Expert Comment

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
``````
0

Author Comment

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

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
``````
0

Author Comment

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

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

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
``````
0

Author Comment

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

Author Comment

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

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
``````
0

Author Closing Comment

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

## Featured Post

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦
###### Suggested Courses
Course of the Month11 days, 11 hours left to enroll