Code not finding correct rows

Posted on 2014-02-13
Last Modified: 2014-02-18
I did not write this code and need help with what it is doing. It looks at another sheet and takes different values from certain rows and pastes them into another sheet.  It is not working correctly and goes to one row to the left for inserting into column D at times.

Private Sub Formatrealize()
Dim RowCount As Integer
Dim Fund As String
Dim Trade As String
Dim TradeRow As Integer
Dim NumCount As Integer
Dim INR As String
Dim USD As String
Dim List As Integer
Dim ColumnCount As Integer

'Clear contents before filter

List = 0
NumCount = Application.CountA(Sheets("Date Input").Range("B3"))
'Count the total numbers of lines for loop
RealizeRaw.Activate 'looks at G-L India Application
RealizeRaw.Range(Selection, Selection.End(xlUp)).Select
ColumnCount = 65536 - Application.WorksheetFunction.CountBlank(RealizeRaw.Range(Selection, Selection)) + 5

For NumRow = 1 To NumCount
Num = Start.Cells(TradeRow + 22, 2)
For row = 1 To ColumnCount
If RealizeRaw.Cells(row, 1) = "EMP:" Then
    EMP = RealizeRaw.Cells(row, 3) '
ElseIf RealizeRaw.Cells(row, 1) = NUM Then
    RealizeRaw.Cells(row, 3).Value = "WORK"
        If RealizeRaw.Cells(row + 1, 10) = "" Then
            INR = RealizeRaw.Cells(row, 9) 'column C
            USD = RealizeRaw.Cells(row + 1, 9)
            INR = RealizeRaw.Cells(row, 10)
            USD = RealizeRaw.Cells(row + 1, 10)
                 End If
    List = List + 1
    RealizeSum.Cells(List, 1).Value = EMP
    RealizeSum.Cells(List, 2).Value = NUM
    RealizeSum.Cells(List, 3).Value = INR ' Column C
    RealizeSum.Cells(List, 4).Value = USD 'column D
End If
Question by:leezac
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
  • Learn & ask questions
  • 5
  • 3
LVL 30

Expert Comment

ID: 39857393
What do you want ?
and what is the problem with this code.

Like it is doing what ? and what you want instead ?

I see you said:
goes to one row to the left for inserting into column D at times.

You meant 1 column to the left ? as one row should be down !!
LVL 30

Expert Comment

ID: 39857405
Furthermore it start with

and realizeSum is a variable that is not declared it must be declared somewhere else in your code not in this sub

So need to know to what sheet this realizeSum variable is assigned to like what sheet are you trying to update beside taking a total from the sheet Date Input


Author Comment

ID: 39857422
Well after I posted I found the issue somewhat.  The sheet that the code is getting values from shifts and adds a column thus throwing the values off that are imported.  I am seeing if the import sheet can be revised.
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

LVL 30

Expert Comment

ID: 39857427
ok if you need help let me know.

Accepted Solution

leezac earned 0 total points
ID: 39857484
 Is the code looking for whether 10 or 9 columns?  The issue is if there are 10 - it is not getting the right values.  It does for 9 columns.

Author Comment

ID: 39857640
That is what I thought it was doing.  Can I change the code to work for 10 columns like it is doing for 10 columns or is it best to get the import sheet where it only has 9 columns?

Author Comment

ID: 39857707
Could I use

        If RealizeRaw.Cells(row + 1, 10) > 0 Then
            INR = RealizeRaw.Cells(row, 10)
            USD = RealizeRaw.Cells(row + 1, 10)
            'RealizeRaw.Cells(Row, 10).Select

Author Closing Comment

ID: 39866924
I am going to try to get sheet replaced or repost when I know more about what is happening with the sheet.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

734 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