Thread: Find function problem

Below is the section of code that I am having problems with. I've got a table of data which i am trying to manipulate onto another sheet in a presentable form to customers. Depending on the header of the column I would like the corresponding value to go in one of two columns. If the header contains 'freq' in the title, (not all are just freq, there are other headers containing that string as well) then I want it to go one side, if it does not then it needs to go on the other.

I have an array with the data, am able to get the function to look at the headers, but the .find function does not seem to return 'True' when the string is present, it just returns all searches as false.

I hope that makes sense, if anyone has any ideas it would be hugely appreciated.

Apologies for confusing use of variables for colNumber and colLetter, there is a seperate function to take the 'aloop' number and return the corresponding column letter, confusingly I decided to call this final variable colNumber - it is in fact just the letter of the column that I want.

            
            Dim aloop As Integer
            Dim valueloop As Integer
                valueloop = 6
            Dim freqloop As Integer
                freqloop = 6
            Dim Found As String
            Dim SearchFor As String
                SearchFor = Workbooks(myFileName).Worksheets("caretaking").Cells(1, 5).Value
            Dim ColNumber As String
                
            
            For m = 1 To intLocationCount
                If labeltemp.Caption = DataCT(m, 1) Then

                    For aloop = 4 To 28
                    
                        ColNumber = ColLetter(aloop)
                        Found = vbNothing
                        With Workbooks(myFileName).Worksheets("caretaking").Range(ColNumber & "1")
                            On Error Resume Next
                            Found = .Find(What:=SearchFor, LookIn:=xlValues, After:=.Cells(1, 1), _
                            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
                            On Error GoTo 0
                        End With
            
                        If Found = True Then
                            Cells(freqloop, 4).Value = DataCT(m, aloop - 1)
                            freqloop = freqloop + 1
                        Else
                            Cells(valueloop, 2).Value = DataCT(m, aloop - 1)
                            valueloop = valueloop + 1
                        End If
                            
                    Next aloop
                    
                End If
            Next m

Main Menu -> Select 'Aldershot' in Location -> Select 'produce costings breakdown'

New book is created with a template sheet, data should be filtered into B and D columns under relevant areas.

The code behind the MAIN MENU form has a 2/3 of the way down marking the mostly relevant area.

I'm pretty new to all this, loops are something I understand for the most part. What do you mean by auto filter the sheet based on the selection?

The ref errors are because i ripped out all the other sheets in the book, its fine though, I just need to know how to get the data into the correct columns.

yes, the entry that is there is the same format for all of them, one line per entry. i just can't figure our why the .find function won't work,

I will try and incorporate that into it, looks good though.

Any ideas why the find function from the original wouldnt work though?