Posts by rossfourfive

Re: Search Range For Part Strings

Superb, that's it working.

Thanks everyone for your help.

Re: Search Range For Part Strings

Pike,

Thats working now, thanks a lot. One more thing...how would i change it so that the case doesn't matter in the search?

Thats one advantage of the find method Stephen is using...

Stephen,

If i type in "Glasgow" it finds them but not for part strings. So if i type "Glas" nothing happens. I'm keen to get this find method working properly as it seems to be the more efficient way of doing it.

Re: Search Range For Part Strings

Pike,

I have played around with

rCell Like "*" & Sheet2.TextBox1 & "*"

and used

cellcheck = rCell Like "*" & Sheet5.TextBox1 & "*"
        If cellcheck = True Then

This does find part strings but i'm having a problem with it repeating rows and not clearing the results before each search. I will have another look at this if we can't get the find to work how i need it to. Thanks for the tip though.

Stephen,

I pasted your code into the workbook and couldn't get it to work. Played around with it a little but still no luck so i have attached a sample book as requested. Cheers for having a look at this.

Charlie,

Haven't had time to check this out but will look at it later, thanks for that.

Re: Search Range For Part Strings

Quote from Dave Hawley

Because you had rated yourself above those answering YOUR question.

Why?

I would consider my knowledge of excel to be above average. My knowledge of VBA however is basic. I apologise for the confusion.

Is there a way to adapt my code to search for a part string or is the only way to achieve this to re-write the code to use find instead?

If i am to use find how do i adapt -

Cells.Find(What:="Cat", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate

- to use the textbox as the search string and to print the results of the search on another sheet as i have done in my example in OP. Also which bit of the code makes it search for part strings?

Thanks

Re: Search Range For Part Strings

Quote from Dave Hawley

rossfourfive, I'll lower your assumed experience as using FIND is basic at best.

Mate, I appreciate you have to enforce your forum rules strictly but i really struggle to understand why this has to lead to the extremely patronising tone you use in almost every single post.

I can use find by following the guide you have written on it which is great but i don't want to use find for this. Is there no way i can adapt the code i am currently using to search for part strings? I know its possible to use *s to search for part strings but can it be used in conjunction with the textbox i am using?

Once again, thank you for your help.

Re: Search Database For Similar Strings

Quote from StephenR

How do you define "similar"? I would imagine using Find would be a better method.

If i enter "ab" in the text box i want it to return all rows with "ab" in any of the cells. So it would return aberdeen, abstract, pablo etc. Any cells with ab anywhere in them would be returned.

How would i use the find method? Is there not a way i can get it to look for part of a string rather than an exact match with the code i already have.

Thanks for your help.

Hi,

I am using the following code to search a database of information and then display it on a different sheet.

The user types the search term into a textbox and then presses a command button to search the database.

Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?

Thanks

Sub SearchDatabase()


Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range


On Error Resume Next


    Set ResultsRange = Sheet2.Range("H13", Sheet2.Range("M65536").End(xlUp))
    ResultsRange.ClearContents
    
    Set rRange = Sheet1.Range("A1", Sheet1.Range("F65536").End(xlUp))
    ResultsOffset = 13
    
    For Each rCell In rRange


        If rCell = Sheet2.TextBox1 Then
        Sheet2.Range("H" & ResultsOffset) = Sheet1.Cells(rCell.Row, 1)
        Sheet2.Range("I" & ResultsOffset) = Sheet1.Cells(rCell.Row, 2)
        Sheet2.Range("J" & ResultsOffset) = Sheet1.Cells(rCell.Row, 3)
        Sheet2.Range("K" & ResultsOffset) = Sheet1.Cells(rCell.Row, 4)
        Sheet2.Range("L" & ResultsOffset) = Sheet1.Cells(rCell.Row, 5)
        Sheet2.Range("M" & ResultsOffset) = Sheet1.Cells(rCell.Row, 6)
        
        ResultsOffset = ResultsOffset + 1
        End If


    Next rCell
    
    ResultsOffset = ResultsOffset - 13
    
    If ResultsOffset = 0 Then
        MsgBox "No Match Found"
        Else
            If ResultsOffset = 1 Then
                MsgBox ResultsOffset & " entry found."
                Else
                MsgBox ResultsOffset & " entries found."
            End If
    End If
    


End Sub

Re: Toolbar Command Button As Toggle Button

Quote from Bob Phillips

You don't need the Toggle variable, just the value of the State property.

Yeah i have since gotten rid of this and use the IF statement to check if the button is up or down and activate the corresponding event.

This works fine but if i save a workbook with the button down and the columns hidden when i open a different workbook the button is still down but the columns visible.

Any way to avoid this?

Re: Toolbar Command Button As Toggle Button

Quote from Dave Hawley

Use a ToggleButton.

It's a custom toolbar not a userform

Re: Toolbar Command Button As Toggle Button

Quote from Bob Phillips

In the button macro, add

Code

With Application.CommandBars.ActionControl
    
        .State = Not .State
    End With

Sorry i'm not sure where you want me to add this.

I should make it clear this is for an add-in toolbar and not a toggle button on a userform.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I have added

With Application. CommandBars.ActionControl 
     
    .State = Not .State 
End With

into the macro i want the button to run.

Now when i press the button it runs the macro and presses the button in. When i press the button again it unpresses but runs the same macro. Is there a way to run a different macro on the unpress?

This is the code i'm using when installing the toolbar:

With NewMenu.Controls.Add(Type:=msoControlButton, before:=6)
        .Visible = True
        .Enabled = True
        .Caption = "Hide/Unhide Columns"
        Sheet1.Shapes("Picture 7").Copy
        .PasteFace
        .OnAction = "HideColumns"
        
    End With

Appreciate your help[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Think i've sorted it, haven't properly tested it but seems to be working well just now.

I declared a static boolean at the start of the macro and this controls which event happens and whether the button presses or unpresses through an if statement.

Thanks to Bob for getting the button to press in and out.

Sub HideColumns()


Static Toggle As Boolean


If Toggle = True Then


With Application.CommandBars.ActionControl 'presses the button in
    .State = Not .State
End With


    Toggle = False ' changes the variable so next time it unpresses the button and runs the other macro


    ActiveSheet.Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Columns("K:M").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Columns("O:O").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Columns("V:AB").Select
    Selection.EntireColumn.Hidden = True
Else


With Application.CommandBars.ActionControl 'unpresses the button
    .State = Not .State
End With
    ActiveSheet.Cells.Select 'activates the reverse macro
    Selection.EntireColumn.Hidden = False
    ActiveSheet.Cells.Range("A1").Select
    Toggle = True 'changes the variable so next time it operates the other macro
End If


End Sub

[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Sorry - automerge post no.4

If i leave the button pressed in when i exit excel, the next time i load up excel the button is still pressed in and so the macros swap round.

Does anyone know of a way to reset the button when i exit excel? Or another way around this?

Thanks

I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.

.State = msobuttondown
.State = msobuttonup

Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.

Appreciate your help.

Re: Fill Empty Column Cells With Reference To Adjacent Cells

Quote from great_big_bear

I would suggest you can protect the "yellow area" (or more) of your sheet very easily. This would prevent the problem even getting to you...

This tends to be my method for this:
1: Select all cells
2: Right click anywhere on the cells (or press Ctrl + 1 - handy shortcut of the day)
3: Go to the last tab and uncheck "protected"
4: OK
5: Select the cells you want to prevent editing (your pre-written formulas)
6: Go through the above process (2 & 3) to redefine these specific cells as protected
7: Now go to Tools -> Protection -> Protect Sheet
8: You can see and define the protections that will be applied to the cells (according to whether you've defined them as protected or not)
8a: Probably no need for a password most of the time - this makes people feel patronised in my experience!

Done.

Notes:
1. As you can see from the methodology, I prefer to unprotect all the cells and only choose to protect the ones with formulae I need to preserve - this means people can add notes, extend tables, generally edit without being denied at every stage which can be very frustrating.
2. I have heard problems with password-protected workbooks being blocked by firewalls (not come across it myself).

Let me know if this works for you.

Display More

When the workbook was first created and sent out to all that needed it the cells were protected but over time they seem to have been unprotected for one reason or another.

The problem with this would mean re-distributing the workbook again which isn't really an option.

Thanks for the tips though and will keep them in mind for future projects.

Re: Fill Empty Column Cells With Reference To Adjacent Cells

Quote from StephenR

Why not just overwrite all the cells, viz:

Code

Range("AD14:AD" & Cells(Rows.Count, 1).End(xlUp).Row).Formula = "=IF(AC14=""n/a"",AB14,AB14*0.9)+V14"

Perfect...

Well that was a lot easier than i thought it would be!

Thanks for your help everyone.

Re: Dynamic Range To Look Past Blank Cells

Okay thanks, i think i have it working now.

I'm using

=OFFSET($A$14,0,0,MATCH("*",$A:$A,-1)-13,30)

Is there a way i can declare and set this at the start of a macro so that i can use it over different workbooks?

Re: Find Empty Cells In Column And Fill With Formula From Other Cell

Quote from Kenneth Hobson

For one column, a method like this might work for you.

Code

Sub test()
  FillEmptyCellsWithFormulas Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
End Sub


Sub FillEmptyCellsWithFormulas(theRange As Range)
  Dim r As Range, sFormula As String
  For Each r In theRange
    If Not (IsEmpty(r)) Then sFormula = r.Formula
    If IsEmpty(r) Then r.Formula = sFormula
  Next r
End Sub

Display More

This works if the cell is empty, however it doesn't paste a relative formula, only absolute. If the cell has a number typed in rather than a formula it doesn't correct this.

I have attached a small example. Column AD contains the total for each row, AD14 has the correct formula, AD15 the number has just been typed in and AD16 the formula has been deleted altogether. My macro needs to be able to fix both of these cells.

Thanks for your help.

Re: Find Empty Cells In Column And Fill With Formula From Other Cell

Quote from great_big_bear

To be clear, are we talking about empty cells, i.e. nothing inside (what you talked about at first), or value-only cells where a formula has been replaced but the cell isn't empty per se (my rant)?

Or both?

Also, as Kenneth said, it would be really good if you could post an example...

It would be both, some cells have had the formula deleted altogether and some have a number typed in instead of the formula.

I will post an example from work tomorrow.

Re: Find Empty Cells In Column And Fill With Formula From Other Cell

Quote from great_big_bear

Mmm, I had a problem like this caused by some clown writing a macro into a workbook which turned off automatic calculation globally... People realised the numbers in their columns weren't right so they just wrote them over the top of the formulae instead of turning calculation back on (something they hadn't understood as an option).

People; don't turn calculation to manual - it is a recipe for retardedness.

Anyway, rant over. You can find blank cells with Stephen's line of code, assuming you understand it!

This is what's happening, they're just typing in the number for some reason or another. I don't think this is because of the automatic calc option though as most of them probably have no idea what that is.

Regarding Stephen's code, what would happen if the cell in B1 has also been edited? Is there a way to have it paste in the formula with the correct cell reference?

Hi,

I am having the same problem as this thread http://www.ozgrid.com/forum/showthread.php?t=26072.

Quote

I've recently been pointed in the direction of dynamic ranges (named ranges that use OFFSET and COUNTA to expand to cover the whole of a list, even if the number of rows used is frequently changed).

They seem to have a big drawback, in that the range will not go to the bottom of the list if the list has blank cells in (because the COUNTA for the column of interest does not equal the number of rows in the list).

Does anyone have a workaround to this?

A typical named range might look like this:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$25),1)

Display More

I tried to post in it but it's too old.

I have been to the link posted in that thread about advanced dynamic named ranges but inserting a row with numbers in it and then hiding it is not suitable for me as i deal with other peoples spreadsheets so need to always be working with the Active Sheet.

Is there another way round this in VBA?

Re: Find Empty Cells In Column And Fill With Formula From Other Cell

I have 30 columns with data in and differing amounts of rows in each workbook, 14 are sent to me each month with different numbers of bookings. The last column in each row (AD) has a final total of various charges.

Each workbook is sent to me by a colleague who processes these bookings. These colleagues are not particularly competent with excel, or indeed computers at all, and as such the workbook tends to be in a bit of a mess when it gets to me. The main problem is the final column (and others) has been messed about with and lots of the cells have had the formulas removed by accident.

I need the macro to find these cells and copy the appropriate formula into them.

Hope this makes sense

Hi,

I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.

I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.

Thanks