Posts by reddale

  • vbYesNo responses

    Re: vbYesNo responses


    Robert


    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

  • VLookUp not returning expected results

    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.


    Regards

  • vbYesNo responses

    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?

  • vbYesNo responses

    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?


    Code
    Select Case Range("m52")
    Case 1
        If MsgBox("YOU HAVE BLANK FIELDS. DO YOU WISH TO CONTINUE", vbYesNo, "BLANK FIELDS") = vbYes Then
        End If


    Regards

  • VLookUp not returning expected results

    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

  • VLookUp not returning expected results

    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


    Quote

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

    Code
    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.


    Quote

    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

  • VLookUp not returning expected results

    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

  • VLookUp not returning expected results

    Hello,


    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?


    Regards

  • Put a formula in a text formatted cell.

    Hello


    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?


    Thanks

  • ComboBox number format

    Re: ComboBox number format


    Thanks mrmmickle1


    I was populating the Combobox via

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

    from the UserForm_Initialize


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

  • ComboBox number format

    Hello


    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.

  • Delete code from a worksheet using the codename for the sheet

    Hello


    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




  • Use the address of the first cell in the same row as the active cell

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


    cytop


    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

    Code
    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.


    Cheers

  • Use the address of the first cell in the same row as the active cell

    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.

  • Use the address of the first cell in the same row as the active cell

    Hello


    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.


    [COLOR="#0000CD"]MODERATOR EDIT


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