Posts by Pesky Weasel


    This code should do the trick but you should only run it once or there will be multiple pp's inserted.
    I assumed that you wanted the Salespersons name left in and I put the sheet name before with a space. Modify the code for your needs.


    Sub myMacro()

    Dim shtName As Variant

    shtName = ActiveSheet.Name

    Range(("b2"), Range("b2").End(xlDown)).Select

    For Each cell In Selection
    cell.Value = shtName & " " & cell.Value

    End Sub

    Edit: Did not open attatchment, and did not realise you wanted a new col inserted. What I have given you will place pp in the same cell as Salesperson NOT in a cell on its own :smash:

    All the very best advice.

    AJW mentioned JWalkenbachs Power awesome publication...but if you are just starting out I would really reccommend Excel Programming for Dummies (by the same author). Ive yet to see a Dummies guide that is not brilliantly set out and easy to follow, added to this JW is kinda like Master Yoda to Excel junkies :beergrin:

    Have fun and dont be afraid to ask. We all bollocksed up the simplest things (and some of us will continue to do so) so we all remember what its like.

    Good Luck


    Not 100% sure what you are trying to accomplish & I am fairly new to VBA but does this do what you want?


    Private Sub PointTypeComboBox_Change()

    If PointTypeComboBox.ListCount <= 0 Then

    With PointTypeComboBox
    .AddItem "Float16", 0
    .AddItem "Float32", 1
    .AddItem "Float64", 2
    .AddItem "Int16", 3
    .AddItem "Int32", 4
    .AddItem "Digital", 5
    .AddItem "String", 6
    End With
    End If

    ActiveCell.Value = PointTypeComboBox.Value

    End Sub:blah:

    PS: What Dave said!!!!!!!!! Not much point repopulating the ComboBox every time when the values wont change.

    Quite correct!!! But I was not sure if all the data was restricted to that format.
    I tried to allow for a large range of tempretures and varying degrees of precision.

    Otherwise what you suggest would definitely be the way to go.


    Howdy All,

    I recently had an OP ask me how to extact the number portion of an imported text string. The text string looks like:

    Temperature of starting experiment: 10.25 °C
    First I suggested text to Columns but also offered this formula:

    =TRIM(MID(A250,(SEARCH(": ",A250)+1),((SEARCH(" °",A250))-(SEARCH(":",A250)))))

    anyone have any sugestions of a simpler approach to this formula??


    This is one of many ways to do this.
    Place this code in the code section of the worksheet you wish to restrict. (right click on the sheet tab - View Code)

    Private Sub Worksheet_Activate()

    Me.ScrollArea = "a1:f100"

    End Sub

    Hope this helps

    I cant believe how much time I wasted with this. :no:

    I checked for spaces before the words but didnt think to check for spaces after. :redface:

    Ummmmm.... It kinda works now.

    :exclamat: This application has encountered a fatal end user error
    and will be shut down until the
    user gets a clue.

    Thanks Again Dave


    Have got it working using a reference


    this only works if I copy/paste special values the text in J180 from one of the cells in the original teams list.
    That is to say, if I select a cell containing Adelaide from the range K4:K179 and paste its value into J180 it works, otherwise it returns zero.

    As Goldmember said 'Ishn't dat weird?'


    I have a column which is populated by the formula below:


    where I am comparing the scores (in col E & J) of two teams to return the winner out of teams in col B & G).

    I then want to count the occurances of a particular value returned in that column. (In this case AFL Teams - GO DOGS).

    I cannot understand why the following will not work:

    Any Ideas

    edit: types like edward scissorhands :rolleyes:

    Howdy All,

    I am trying to close a workbook in a sub() using the line:

    workbooks("thisbk").close savechanges:=false

    but I also has a private sub in the 'this workbook before close' section that also contains the same line. This seems to confuse excel as nothing closes. I am trying to avoid using the application.quit avenue as my boss lost an hours work the other day (coz he didnt read the save changes dialog).

    Any ideas why this would not be working? Or how to work around it?


    Following code by Bill Jelen works OK if you want either highlight or no fill at all.


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not OldRng Is Nothing Then
    OldRng.Interior.ColorIndex = xlNone
    End If
    Target.Interior.ColorIndex = 36
    Set OldRng = Target
    End Sub

    Dave, (and anyone else intersted)

    It seems that my understanding of the problem was in error. Heres what appears now to have been happening.

    I had a Spin Button which changed a value in a cell and then ran a macro that used that value (part of the macro unprotected the sheet and then protected it again when finished).

    But what was happening was when the user (OK - me!!!) clicked the down button and the spinner value was already at its minimum value, all it did was select the control. it appears that while the control was selected trying to unprotect the sheet caused an error.
    I fixed this by putting a line under the control onfocus event that simply selected a cell on the worksheet. This made the error go away once and for all.


    Dear All,

    The following code (thanks Dave) isnt doing what I want it to do. I would like it that if the file existed it would move to section of the sub named continue, if the file doesnt exist it runs a seperate sub that creates a new one.
    Any ideas?

    fName = Range("F3").Value & ".xls"
    With Application.FileSearch
    .LookIn = "G:\john\paul\peter\data"
    .FileName = fName
    If .Execute &gt; 0 Then
    GoTo conTinue
    Exit Sub
    End If
    End With

    Hiya All,

    I have a worksheet that has macros that turns worksheet protection for contents on and off.
    When the file is in read only mode that protection causes errors. Is it possible to have use worksheet protection in a readonly workbook


    Edit: OK, I couldnt understand why this is happening so I went back and kept trying to perform the action (clicking on a control) that makes it crash.
    It only comes up with an error occasionally now. I doesnt seem to matter which cell is active at the time, it just seems to occur randomly:puzzled:
    Any ideas?????

    No, No, No you should definitely change it. I mean when I run VBA code that I downloaded from a stranger on the internet I always know that as long as his name is harmless and cute then the code is assured to contain no malicious routines.:lol:
    There is no need to be careful about VBA viruses as long as the name is good!!!