Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Code not finding correct rows

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
leezac
Asked:
leezac
  • 5
  • 3
1 Solution
 
gowflowCommented:
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
 
gowflowCommented:
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
 
leezacAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:
ok if you need help let me know.
gowflow
0
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
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
 
leezacAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now