0

I have the start of some VBA which is almost there.

I want to get along the first row and find a word in a number of columns, "Fund". If the cell contains "Fund", to then go down the column to whatever cells contain "1" and pull through data +1 and +5 cells away in the same row.

It works for unique column names, if i change for example the code to Fund1 and cell Q1 to Fund1 it works. But not for more than one cells containing "Fund".

I need to set up some kind of loop so that it carries on finding the word "Fund" after it completed the first time it sees it.

Sub consolidate()

Dim FindRow As Range
Dim rowloc As Integer
Dim c As Object
Dim fundrow As Integer
Dim count As Integer
count = 1
fundrow = 1
Dim isin As String
Dim weight As String
Set Sht = ActiveWorkbook.Worksheets("LM Holdings")


Sht.Range("A:B").ClearContents

            Sht.Activate
            Set c = Sht.Rows(1).Find(What:="Fund", LookIn:=xlValues)
            If Not c Is Nothing Then
                Set FindRow = Sht.Rows(1).Find(What:="Fund", LookIn:=xlValues)
                rowloc = FindRow.Column
                Do Until count = 10
                    If Cells(count, rowloc).Value = "1" Then
                        isin = Cells(count, rowloc + 1).Value
                        weight = Cells(count, rowloc + 5).Value
                        Sht.Activate
                        Cells(fundrow, 1).Value = isin
                        Cells(fundrow, 2).Value = weight
                        fundrow = fundrow + 1
                    End If
                        count = count + 1
                    Loop



                     End If


                     End Sub
halfer
  • 19,824
  • 17
  • 99
  • 186
Harry OC
  • 1
  • 2
  • 1
    you want [`FindNext`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.findnext) – Scott Craner Sep 05 '19 at 17:30
  • 1
    also, [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Cyril Sep 05 '19 at 17:58
  • I think the range of the first row would normally be referred to as `Rows(1)` so it could be, `Set c = ActiveSheet.Rows(1).Find(What:="Fund", LookIn:=xlValues)`, also when you are refering to cells like `Cells(Row, Column)` that is always taken as the cell on the Activesheet. It is much safer to create a variable tied to the Worksheet and refer to that with `Cells(...` like this: `Set Sht = ActiveWorkbook.Worksheets("LM Holdings")` and then refering to a cell on that sheet would simply be `Sht.Cells(Row,Column)`. This is how you avoid `.Select` as @Cyril pointed out as well. – Andras Sep 05 '19 at 18:21
  • What you are trying to achieve, could also be done by finding the last used Column in Row 1, and save that as a range, and go through the whole range by `for each cell in range`, to find all cells with `"Fund"` – Andras Sep 05 '19 at 18:31
  • Is Sheet called "LM Holdings" the only sheet you are working with? Isn't the cell defined by DealingToolRow on a second Worksheet? – Andras Sep 05 '19 at 20:07
  • What is the Range of columns that are supposed to be searched for the word "Fund", (for example: from Column "A" to Column "F") ? – Andras Sep 05 '19 at 20:55
  • Thanks for your comments, I do not know much about vba so I am manipulating code from another similar process. @cyril I have edited the code to reflect your suggestions (I think). – Harry OC Sep 06 '19 at 08:12
  • @ScottCraner , FindNext sounds like the solution, how would I add it into the code above? – Harry OC Sep 06 '19 at 08:14
  • @HarryOC Remove all of your `Sht.Activate`... those are redundant and only slow down your code by forcing excel to display those items when it isnt' needed. the point of setting the sheet is so that you can utilize said sheet with a shorthand and avoid using select or activate. you don't need to activate or select an object if you can act upon it appropriate. `sheets(1).cells.select // selection.copy` could be shortened to `sheets(1).cells.copy` as you have fully qualified the object and provided the action to occur on said object. – Cyril Sep 06 '19 at 13:13
  • Is "LM Holdings" the only Worksheet involved in this? Or are you collecting the found information on another Worksheet? Or, is `Sht.Range("A:B").ClearContents` making space on the same sheet for the results? @Harry OC – Andras Sep 07 '19 at 19:50
  • @ScottCraner any more thoughts on how to implement FindNext into the above code? – Harry OC Nov 03 '20 at 13:53

0 Answers0