Posts by 043792

  • Rename Cell Based on Cell Content

    Re: Rename Cell Based on Cell Content


    Will, your method is more efficient but the code was a snip from a more complex operation I just had a brain block on rewriting the cell contents.
    Thanks for the help

  • Rename Cell Based on Cell Content

    I am trying to rename the contents of of each row in column A
    All I really want to do is add a letter at the end of each of the cells
    For example if A2:A8 contains
    1
    5
    8
    11
    45
    56
    76
    I would like it to end as
    1T
    5T
    8T
    11T
    45T
    56T
    76T


    My non working attempt and example sheet attached


  • Request for some insite/advice on my VBA code

    Re: Request for some insite/advice on my VBA code


    Trying out your code...
    any idea why you get a Run-time error '1004'
    Select Method of Range Class Failed
    on the following lines of code


    Code
    Range("A5").Select


    and

    Code
    Range("A1").Select


    You can get around it by adding the sheet reference in front but not sure why that is necessary

    Code
    Sheets("Ad_indices_labour_2005").Range("A5").Select

  • Request for some insite/advice on my VBA code

    Re: Request for some insite/advice on my VBA code


    Thanks for looking. I like your way of doing this.
    I was just experimenting with the End(xlDown) command and find it very useful.
    The extra table was in existance when they turned this project over to me, apperently they need to see the table data

  • Request for some insite/advice on my VBA code

    Re: Request for some insite/advice on my VBA code


    Thanks for looking ... Thats what i think it should do as well, but try the example, run it in debug and you will see the value of a3 gets poped into a36 when FINDER is reset to A3 in the loop. The value in A36 changes as the loop progresses to the value of A3 thru A31. Must be someting I have done but I cant see it.
    I had originally done

    Code
    Set FINDER = Sheets("TOTALS").Range("A3")


    but found that my header in A3 kept changing so I moved it to A36 out of the range that I care about


    Here is the entire VBA code


  • Request for some insite/advice on my VBA code

    Would really appreciate some help with this, I am kinda new to VBA but I have managed to get the sheet to mostly work :?
    I have attached a sample sheet of what I am trying to do.
    Basically I am building row and column heads on TOTALS sheet using data from the labour sheet. The row headers can change from month to month and will change between various functions in the company.
    The TOTALS sheet data is a sum based on row headers across the parts and labour sheets.
    Hope seeing the example make those words clearer.
    I have added comment to the code to help explain what I am doing
    Here is my problem, If I push the button on the start page to build my totals page it goes of and executes the code and builds the sheet but for some reason it seems to loop back to the begining and erase the sheet ????
    If I Step the code in debug the sheet builds properly.
    There is also this question in the code ...

    Code
    '***** why does the following line poke the value of "FINDER" into cell A36?
    
    Set FINDER = Sheets("TOTALS").Range("A36")

  • Search UserForm: Search Worksheets

    Re: search form in vba for a search in 20 sheets


    You are good my friend. I could see the error but could not find the cause.
    Your solution is excellent but being somewhat anal I decided to fix the cause.
    changed


    Code
    UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 2) = Data.Parent.Name & "!" & rngFind.Address


    to


    Code
    UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 2) = rngFind.Address


    I used to program in Basic and then Quick Basic but was out of the programming game by the time Visual Basic came out. Time for some courses...
    Thanks again

  • Search UserForm: Search Worksheets

    Re: search form in vba for a search in 20 sheets


    Thank You so much for your help. Looks like evrything works perfectly.
    One more question if I may....
    I created my large spreadsheet with names that have spaces. I just learned that this seems to cause Runtime error '1004'
    Method 'Range' of object '_Global' failed
    Is there any way to fix this short of renaming most of the 140 sheets and then correcting all the

    Code
    Private Sub CommandButton2_Click()
Sheets("LEXMARK OPI").Select
Sheets("LEXMARK OPI").Range("A1").Select
End Sub


    for multiple buttons on most of the 140 sheets (Fair bit of work even using find/replace all)


    Sample sheet included
    search for harry
    you can select the hit from sheet_one but if you select the sheet two (no underscore so two words) you see the error.

  • Search UserForm: Search Worksheets

    Re: search form in vba for a search in 20 sheets


    This is exactly the code I an looking for to manage a search of my 140 sheet workbook. I hope someone can help me answer a question and solve a problem I am having. Can someone tell me why the code for a macro does not behave the same as the code for a command button. I have used the above spreadsheet as an example and attached it. The macro works fine but command button requires variables to be defined. I have added the following lines to the command button code which eliminated the compile error.

    Code
    Dim ListBox1 As Variant
    Dim TextBox1 As Variant


    but now I get a runtime error '424' object required
    Any suggestions or insite would be appreciated