Solved

Code not finding correct rows

Posted on 2014-02-13
8
175 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
  • 5
  • 3
8 Comments
 
LVL 29

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 29

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

Expert Comment

by:gowflow
ID: 39857427
ok if you need help let me know.
gowflow
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now