Posts by jjst34

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • extracting some data

    Hi,, I have a large spreadsheet, that I'm looking to extract some info from, into another sheet in the same workbook... Basically waht I want to do is move info from sheet 'sec8' to 'parsed'.
    I would like it to lookup in column B to find "TOTAL FOR HOUSING AUTH:"
    Extract that plus 2 digits which are always in the same place, so some type of offset, if I do a Ctrl + Up, then down 2 rows, right one column, and the first 2 digits in that cell.
    Then also extract the info in the next 3 columns C-E, which are dollar values next to the "TOTAL FOR HOUSING AUTH:"


    In the end I want the new sheet 'parsed' to be like this
    -----------columnA B- C D
    TOTAL FOR HOUSING AUTH: 02 $xxx $xxx $xxx
    TOTAL FOR HOUSING AUTH: 09 $xxx $xxx $xxx





    Any help is very much appreciated!!!

  • defined name to multiple sheets?

    Re: defined name to multiple sheets?


    what worked for me was 2 things.. Name them all in one sheet.. get rid of the rest (if you can), then copy the 1 w/ the names in it,, and they copied over..
    Or..
    You can use that "name manager" shades recommened.. Which sorta worked.. it got the named range in all the sheets.. but when I went on that cell it wasn't there,, however when I went to each sheet afterwards and named it, it stayed in all sheets once saved.....

  • VBA ISNUMBER, ISTEXT functions

    Re: VBA ISNUMBER, ISTEXT functions


    Quote from anson10x

    I am trying to create code that deletes lines in a spreadsheet when the first column is either blank or has text in it. However, when writing the VBA code, ISNUMBER and ISTEXT do not work. Amazingly, ISBLANK does work.


    Any clues will be greatley appreciated.



    when the entire column is blank or has text? Or if a cell in column a is blank/text it deletes that entire row?

  • best way to learn VBA?

    Re: best way to learn VBA?


    thanks richie! no doubt the best way for me to learn it is by doing! I try to solve some of the issues here, but sometimes I just don't understand what they're aiming for.. And there are only so many problems I can dream up for my own situations.

  • vba search and delete code doesn't work

    Re: vba search and delete code doesn't work


    Here is one i use and it works great..


  • best way to learn VBA?

    I have been coming to this forum for a little while, and love it.. I have learned soo much.. But honestly, my code still sux! LOL... I really want to learn this very effectively. I love writing it, but when I see some of the stuff you guys come up w/ I am in awe. Where would be the best place to go to learn all the ins and outs of vba. i get all the little email updates and read the forums daily. I really want to take a course though. Are the vba training classes offered on this site going to take me step by step on how to create the most effective code? I am really considering buying one of the training packages. Is there a better place for my lack of money to be spent learning this? Thanks for any suggestions!

  • defined name to multiple sheets?

    Re: defined name to multiple sheets?


    I have vba enter a vlookup in a cell then paste it to about 200 different cells w/ no real pattern, so I figured the easiest way was to name them and reference the name putting the cell reference in vba. I'm sure there is an easier way,, feel free to give input.. but this does get the job done! LOL


  • defined name to multiple sheets?

    Hi there,
    I am trying to give certain cells in EVERY sheet the same name. When I originally set this up, it worked out fine. Now that I am trying to add new names to each sheet, it seems to only want to save to the active sheet. I tried a 'select all' sheets and then go to 'insert - name - define', but it only saves to one sheet.. I am wondering if anyone knows how to save the same name to say, cell A1, in every sheet. I have attached a screen shot of my define names box, and you'll see the name jw21.5 isn't saved quite the same. Thanks for any help!

  • Macros, lookup and if?

    Re: Macros, lookup and if?


    so your basically taking all the main sheet info, and adding a little extra info to it (rows Z thru AC). and you'd like that info to be added automatically?

  • macro not working if formula bar is used

    Re: macro not working if formula bar is used


    just an update.. I tried using the following:


    Private Sub worksheet_change(ByVal Target As Excel.Range)
    If Range("FY05_deducuction_percentage").Value < 0.95 Then
    MsgBox "FY06 percentage has dropped below 95% of FY05 budget" & vbCrLf & "Please adjust the last figure entered!!!", _
    vbExclamation, "Please edit the last number"
    Application.Undo


    but now I can change a figure, it will go below 95%, and won't give a message until I move the selection. which sounds fine,, but now if I just change something and then hit close, save. It allows the change, then next time the sheet is opened, the message comes up, but it can no longer undo the change.. Any ideas?? Thanks!

  • macro not working if formula bar is used

    I have a piece of code as follows


    Code
    If Range("FY05_deducuction_percentage").Value < 0.95 Then
    MsgBox "FY06 percentage has dropped below 95% of FY05 budget" & vbCrLf & "Please adjust the last figure entered!!!", _
    vbExclamation, "Please edit the last number"
    Application.Undo


    it works fine,, until somebody enters values in the formula bar, then the macro won't notice the value is less than 95%.. How can this be corrected? Thanks!