Posts by reddale

    Re: vbYesNo responses


    I'm not sure what is going on. When I comment out your code (and mine when I put it back in for a test) the rest of my code works fine.

    When No is clicked in the message box, it stops the code like I require, but in your code or mine, clicking Yes does the same thing. The rest of the code doesn't run.

    I stepped through all the code and didn't get any clues at to why Yes stops the code, just like No does.

    Back to Google

    Re: VLookUp not returning expected results

    Thank you Rory. I don't think I'll ever get to the level of you guys. Thats why I come here. Thank you for the code. It sure is shorter than mine. I'll have to learn a bit more a bout your code whe I get a chance.


    Re: vbYesNo responses

    Thanks Robert. Your line of code stops rest of the code from running when you click no, but when I click Yes, nothing happens. There is a fair bit of code after what we are talking about. The rest of the code doesn't run. If it makes a difference, I am calling this message box via Case Select

    Any suggestions?

    I am calling a vbYesNo message box.

    If the response is Yes, the rest of the module runs as it should. That is what I want to happen on a Yes response.

    I would like a No repsonse to exit the Sub, but at the moment, when either Yes or No is clicked, the rest of the module runs.

    How do I get A No response to Exit Sub?

    Select Case Range("m52")
        Case 1
            End If


    Re: VLookUp not returning expected results

    While I was trying to make my above answer make sense (still don't know if it does)

    I came up with a solution that invovles Data Validation.

    Once I did that, my code does what I wanted to do. I also found a couple of errors in related code, so a couple more things work better now as well.

    With my issues sorted, the new code (2nd lot in the original post) runs in half the time that the old code I was using that placed VLookUp formulas into the cells did. I don't know if half a second is significant when talking about VBA, but thats' the result I got

    skywriter, your suggestion about the string variable got the ball rolling.

    Thank you

    Re: VLookUp not returning expected results

    Bear with me, there are a few answers

    Quote from skywriter;772749

    I could probably figure this out in just a few minutes with a workbook, but my hunch is your problem lies with what you are trying to look up.

    I started to make a sample workbook to post, but it ends up being too large and the names, addresses etc of our clients are too numerous to replace.

    In the instance of the first piece of code I posted, it is text formatted as General

    In the instance of the second piece of code I posted, it is a number formatted as General


    First why does what is in this cell have to be declared as a string?

    Dim acqstk As String
    acqstk = Sheet17.Range("b40")

    Instead of putting that variable in the lookup you could just simply put Sheet17.Range("b40").Value instead.

    Changing this worked...but

    This is where I get confused because of lack experience I guess

    When the first sample of code I posted runs (via a Worksheet_Change event) it works. When I say it works, the user enters a name in a cell and the other cells poplulate with data from another sheet. It works whether the range is sorted or not.

    When the second code sample runs (fired by clicking a command button) and the user enters a number in a cell, nothing happens. Leave that number in the cell and run the code again and it works.


    You might also want to comment out your on error to see if you get any errors and what they are and what line they are on.

    I had gone down that roadalready, the error appears in the first line of the VLookUp code until a number is entered in B40 and the code is ran again, then there is no error

    Re: VLookUp not returning expected results

    Quote from reddale;772719

    but where the VLookUp should be returning a result it's doing nothing

    Quote from skywriter;772743

    But you don't tell us where that is, so?

    In the second group of code, VLookUp isn't returning a result, it isn't doing anything


    I have this code. It does exactly what is required even if the range("a2:d15") is not sorted.

    Then I have this code that doesn't work properly. It works to a certain point, but where the VLookUp should be returning a result it's doing nothing. The Application.WorksheetFunction.VLookUp looks to my eye to be the same in both lots of code.

    Any suggestions as to why the second lot of code doesn't play?



    I have spent hours trying to work this out myself to no avail.

    I have a cell formatted as text, to allow the use of leading zero's (with no fixed format, there might two leading zero's and 8 other didgits, or one zero and two other digits or no leading zero, so using 00### or the like doesn't help). That's fine 98% of the time, but on occasion the user needs to search for data using a vlookup formula. To make this happen, a macro is used to add a formula to the cell (and remove the formula after the task is complete).

    As soon as the formula is placed in the text formatted cell, everyone knows the formula will be displayed instead of the result.

    What can I do programmatically to stop the formula being displayed instead of the result, while maintaining the text format of the cell?


    Re: ComboBox number format

    Thanks mrmmickle1

    I was populating the Combobox via

    Acquisitioncbo.List = Sheet7.Range("k1:k500").Value

    from the UserForm_Initialize

    When I changed to using RowSource it picked up the formatting.


    I have a ComboBox in a UserForm that gets its values from a range in a work sheet via UserForm_Initialize.

    On the worksheet, the values are fomatted as "0000000" to get a leading zero, but the formatting is lost between the worksheet and the ComboBox ie 0681212 becomes 681212 in the ComboBox.

    Formatting the worksheet range as text works, but to save the user having to enter 500 numbers each time a new set is required, the 500 numbers are created based on the first number and then a simple +1 formula is used to create the remaining numbers.

    If the range is formatted to text the +1 formula doesn't work.

    How do I ensure the format is the same in the combobox as the range on the worksheet?

    Thanks in advance.


    I have the below code that works to delete the code from a worksheet called 142959.

    I want to reference the sheet by it's codename (Sheet13) rather than it's sheetname, because the sheetname will be different each time, but the codename will always be the same.

    I'm sure it's simple, but I can't get it.

    Thanks in advance

    Re: Use the address of the first cell in the same row as the active cell


    Thank you for persevering.

    The cell references are hard coded because the information I want will be in that cell everytime the macro runs, it will be different data everytime, but in the same cell.

    The data will always be found, it has to be entered everytime.

    The code works exactly like I want it to, right up to point where I want to use the formula.

    After 40 minutes of trying to write a response that made sense, I had a brain burp and came up with a way to do it that didn't involve a formula.

    I used

    It does what I want it to do, it's a bit slowish, but it does it.
    The original code Iposted with the IF formula only went as far as

    ActiveCell.Value = Sheets("new disposal entry").Range("B43")

    I've added the rest since.

    Than you for your patience and the shove I needed to work it out.


    Re: Use the address of the first cell in the same row as the active cell

    cytop, because of my limited knowledge of vba, I confuse myself, so it's little wonder you are confused. I have the formula in a macro that finds the cell I want it to find. Then I want the formula to be placed in that cell (which I have succeeded with). where it gets tricky (and stops working), the "logical test" cell address will be different every time (the first cell in the active row is a constant, but the row will be different each time.

    This code works as I want it to, except for the logical test in the IF formula. It has to be the first cell in the active cell's row

    Clear as mud now, I'll bet.


    My goal is to use the cell address of the first cell in the same row that the active cell is in, as the cell address for the "logical test" part of an IF formula.

    I've tried to make myself clearer, but it justs ends up looking more confusing.

    I think it would be something like

    =IF("first cell in current row"='new disposal entry'!R40C2,'new disposal entry'!R43C2,"""")

    Thanks in advance.


    Please do not use code tags with formulas - - they are reserved for use only with VBA code.[/COLOR]