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


Code not finding correct rows

Posted on 2014-02-13
Medium Priority
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 31

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 31

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.
Technology Partners: 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!

LVL 31

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

719 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