If Sheet1.Cells(row + 1, 10) = "" Then
...
ColumnCount = 65536 - Application.WorksheetFunction.CountBlank(RealizeRaw.Range(Selection, Selection)) + 5
temprows = 1
For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then
If RealizeRaw.Cells(Row + 1, "J") <> 0 And RealizeRaw.Cells(Row + 1, "J") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
INR = RealizeRaw.Cells(Row + 1, "J")
USD = RealizeRaw.Cells(Row, "J")
ElseIf RealizeRaw.Cells(Row + 1, "I") <> 0 And RealizeRaw.Cells(Row + 1, "I") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
INR = RealizeRaw.Cells(Row + 1, "I")
USD = RealizeRaw.Cells(Row, "I")
End If
RealizeSum.Cells(temprows, "A") = fund_id(0)
RealizeSum.Cells(temprows, "B") = fund_id(1)
RealizeSum.Cells(temprows, "C") = fund_id(2)
RealizeSum.Cells(temprows, "D") = INR
RealizeSum.Cells(temprows, "E") = USD
temprows = temprows + 1
End If
Next
'Sort and Seperate Fund Numbers
RealizeSum.Activate
...
temprows = 1
For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then
If RealizeRaw.Cells(Row + 1, "J") <> 0 And RealizeRaw.Cells(Row + 1, "J") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
INR = RealizeRaw.Cells(Row + 1, "J")
USD = RealizeRaw.Cells(Row, "J")
ElseIf RealizeRaw.Cells(Row + 1, "I") <> 0 And RealizeRaw.Cells(Row + 1, "I") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
INR = RealizeRaw.Cells(Row + 1, "I")
USD = RealizeRaw.Cells(Row, "I")
End If
If INR <> "" Or USD <> "" Then
RealizeSum.Cells(temprows, "A") = fund_id(0)
RealizeSum.Cells(temprows, "B") = fund_id(1)
RealizeSum.Cells(temprows, "C") = fund_id(2)
RealizeSum.Cells(temprows, "D") = INR
RealizeSum.Cells(temprows, "E") = USD
temprows = temprows + 1
End If
INR = ""
USD = ""
End If
Next
temprows = 1
For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then
If RealizeRaw.Cells(Row + 1, "J") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
INR = RealizeRaw.Cells(Row + 1, "J")
USD = RealizeRaw.Cells(Row, "J")
ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" And RealizeRaw.Cells(Row, "J") <> "R " Then
INR = RealizeRaw.Cells(Row + 1, "I")
USD = RealizeRaw.Cells(Row, "I")
End If
If INR <> "" Or USD <> "" Then
RealizeSum.Cells(temprows, "A") = fund_id(0)
RealizeSum.Cells(temprows, "B") = fund_id(1)
RealizeSum.Cells(temprows, "C") = fund_id(2)
RealizeSum.Cells(temprows, "D") = INR
RealizeSum.Cells(temprows, "E") = USD
temprows = temprows + 1
End If
INR = ""
USD = ""
End If
Next
temprows = 1
For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then
If RealizeRaw.Cells(Row + 1, "J") <> "" Then
INR = RealizeRaw.Cells(Row + 1, "J")
USD = RealizeRaw.Cells(Row, "J")
If RealizeRaw.Cells(Row, "J") = "R " Then INR = ""
ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" Then
INR = RealizeRaw.Cells(Row + 1, "I")
USD = RealizeRaw.Cells(Row, "I")
If RealizeRaw.Cells(Row, "J") = "R " Then INR = ""
End If
If INR <> "" Or USD <> "" Then
RealizeSum.Cells(temprows, "A") = fund_id(0)
RealizeSum.Cells(temprows, "B") = fund_id(1)
RealizeSum.Cells(temprows, "C") = fund_id(2)
RealizeSum.Cells(temprows, "D") = INR
RealizeSum.Cells(temprows, "E") = USD
temprows = temprows + 1
End If
INR = ""
USD = ""
End If
Next
temprows = 1
For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) Then
INR = ""
USD = ""
If RealizeRaw.Cells(Row + 1, "J") <> "" Then
INR = RealizeRaw.Cells(Row + 1, "J")
USD = RealizeRaw.Cells(Row, "J")
ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" Then
INR = RealizeRaw.Cells(Row + 1, "I")
USD = RealizeRaw.Cells(Row, "I")
End If
If RealizeRaw.Cells(Row, "J") = "R " Then USD = ""
If INR <> "" Or USD <> "" Then
RealizeSum.Cells(temprows, "A") = fund_id(0)
RealizeSum.Cells(temprows, "B") = fund_id(1)
RealizeSum.Cells(temprows, "C") = fund_id(2)
RealizeSum.Cells(temprows, "D") = INR
RealizeSum.Cells(temprows, "E") = USD
temprows = temprows + 1
End If
End If
Next
For Row = 1 To ColumnCount
If RealizeRaw.Cells(Row, 1) = "Fund:" Then
fund_id = Split(RealizeRaw.Cells(Row, "C"), "-")
End If
If IsDate(RealizeRaw.Cells(Row, 1)) And Cells(Row, 1) = Sheets("Date Input").Cells(3, "B") Then
INR = ""
USD = ""
If RealizeRaw.Cells(Row + 1, "J") <> "" Then
INR = RealizeRaw.Cells(Row + 1, "J")
USD = RealizeRaw.Cells(Row, "J")
ElseIf RealizeRaw.Cells(Row + 1, "I") <> "" Then
INR = RealizeRaw.Cells(Row + 1, "I")
USD = RealizeRaw.Cells(Row, "I")
End If
If RealizeRaw.Cells(Row, "J") = "R " Then USD = ""
If INR <> "" Or USD <> "" Then
RealizeSum.Cells(temprows, "A") = fund_id(0)
RealizeSum.Cells(temprows, "B") = fund_id(1)
RealizeSum.Cells(temprows, "C") = fund_id(2)
RealizeSum.Cells(temprows, "D") = INR
RealizeSum.Cells(temprows, "E") = USD
temprows = temprows + 1
End If
End If
Next
Title | # Comments | Views | Activity |
---|---|---|---|
Auto Operation of MS Excel 2013 | 2 | 29 | |
How to return the focus to the row of the datagrid (vb6) even though the SQL data rows exceed the max windows rows displayed. | 16 | 33 | |
Top 10 with duplicates in Excel Table | 4 | 10 | |
Formula to convert ranked data | 2 | 10 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
19 Experts available now in Live!