Solved

Code not finding correct rows

Posted on 2014-02-13
8
180 Views
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
RealizeSum.Select
'Cells.ClearContents

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("I65536").Select
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)
               Else
            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
Next
Next
0
Comment
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
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 31

Expert Comment

by:gowflow
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 !!
gowlfow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39857405
Furthermore it start with
RealizeSum.Select

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

regards
gowflow
0
 

Author Comment

by:leezac
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Expert Comment

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

Accepted Solution

by:
leezac earned 0 total points
ID: 39857484
gowflow
 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.
0
 

Author Comment

by:leezac
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?
0
 

Author Comment

by:leezac
ID: 39857707
Could I use

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

Author Closing Comment

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

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

615 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