Posts by smuzoen

    Re: cell looks empty but isn't


    Hi there
    counta reports those cells that are not empty so getting a value of 0 is correct if the cells are all empty. To count the blank cells you would use countblank. Do you mean when you do counta on a range of supposedly blank cells you get a number greater than 0?

    Re: VBA Filter By Value Formula in sheet


    Hi there
    The way to get the value from the drop down box is as follows then you can apply this value to the filter you have applied. I think this is what you want. Set the Drop Down 1 box to the following macro.


    Of course your sheet will contain more data than the sample you supplied. You can set the range to filter by finding the last row in a filtered list and this code will do that
    I hope this helps

    Re: Extract specific numbers from a cell


    Hey Ben
    Not completely sure what you want but this may help. If you want to know if "12" is in the string TR123456 and if so put "12" into A2. If the TR123456 string is in A1 and 12 is in B1 then the formula in A2 would be
    =IF(FIND($B$1,$A$1,1)>0,MID($A$1,(FIND($B$1,$A$1,1)),(LEN($B$1))),"")
    If you need to seperate the characters and numbers you could find the position of the first digit in the string with the following array formula if the string is always in the format of 2 characters and then 6 numbers
    =IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*OR(ISNUMBER(1*LEFT(A1,1)),ISNUMBER(1*RIGHT(A1,1)))
    NOTE: Make sure you hit ctrl shift enter when you use this formula as it is an array formula


    Hope this helps

    Re: Data grabbing from a CSV file


    Hi GazF
    You certainly can do this however depends what you mean by collate a months worth of data. You can read in a csv file into excel - just use file open in excel - then select csv file then once data is in excel you can manipulate as required. To help any more requires either a sample file and some details of what you want to subsequently do with the data.
    If you want some help it is best to upload some example files (workbook/csv) and be explicit about what you want to achieve

    Re: error 400 when closing userform


    Hi bdtheoba,
    The problem you have is a bit complicated and is due to the way you have structured your VBA.
    Instead of running your macros in the sheets you should insert a module and take all the subs out of sheet 3 and place them into this module. Instead of using the intialise declaration to set the values of all your combo boxes and text boxes for the AddProjectForm set the values in the module. That will only leave the click events in the AddProjectForm form.
    Instead of showing the form, load the form and then set all the values for the form controls in the module THEN show the form. Once you have finished with the form then unload it.
    The way I structure using a form is this
    1. Insert a module and set all the values for controls on the form (after you load NOT show the form)
    2. Once all the control values are set then SHOW the form
    3. Use the form to collect the inputs then return back to the module to carry out what you want done with the data.
    Error 400 usually occurs when trying to show 2 forms at once and the way you have written the vba there is some conflict I think between AddProjectForm and the AddPeopleForm.
    The code for the module would be as follows


    Try the attached workbook - it is by no means fixed but it should get you on your way - the problem I think is in the way you have structured your VBA. You will notice when you close your AddProjectForm now there is no error 400.
    I hope I have explained this well enough - the main point is the structure - You need to use a module for what you are doing.
    Other input from other members may make this clearer however feel free to ask me questions
    Hope this gets you going in the right direction

    Re: Change Picture in Excel


    This will get you started. This will insert a picture and place it at A1.


    To change pictures you could have different buttons for different positions. You can place a button on the sheet from the Developer Tab - Insert Button - then attach this macro above to the button.
    This should get you on your way.
    Hope it helps

    Re: Selecting a data range without using a loop


    What I meant was find the last row in the filtered data set and then copy cell by cell. You can copy cells even if they are hidden. As I said mine was a very simple example. The point I was making is that you can reference cells and copy them even if they are hidden.
    Irrespective Roberts example is really a better way than mine as looping through cells is not great programming practice. Ranges should always be used if possible as in Roberts example

    Re: Selecting a data range without using a loop


    What you need to do is find the correct last row position even if it is filtered. Use this to find the last used row

    Code
    Dim lastUsedRow As Long
    lastUsedRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count


    Then use the lastUsedRow variable to loop through from 1st row to last row. Something really basic would be (assuming filtered list in in column 7 starting at row 1) - this would copy the data to column 9 starting at row 33

    Code
    Sub testcopy()
      Count = 33
      For k = 1 To lasUsedRow
    Cells(k, 7).Select
    Selection.Copy
    Cells(Count, 9).Select
    ActiveSheet.Paste
    Count = Count + 1
    Next k
    End Sub


    This is really just a basic sample but it may get you on your way - the point is you can copy a cell even though it is hidden - in terms of setting a range Im not sure you could do that if the column is filtered. See if this gets you on your way - if not get back to me

    Re: Copy formula down column based on criteria in row and loop for 12 columns


    Hi there
    If I have you right you want to copy the formula in row 2 in the respective column to be copied from row 6 to final row with data in that column where "Act" is in row 4 in that column.
    If I have your directions correct then try this - will go from column I to column R. You may have to play with code if it is not exactly as you want



    Hope this helps

    Re: Using Excel to count the amount of selections in a drop down list.


    Have a look at the attached workbook. To dynamically update the list requires macros in the sheets as well as in a module to account for worksheet changes.
    Try the workbook and see if it does what you want. I went ahead and assumed that you wanted a solution to see if the chosen club was full therefore remove it from the drop down list. If you want to add clubs to selection then you can just add the club to sheet 3 and it will then get incorporated into the dynamic list
    Hope this helps - if it needs changing let me know

    Re: Find Value in a Column, Make Separate Averages That Skip Those Found Values


    Did you use the spread sheet I uploaded - I have run it and it finds all the averages. The reason I place a -1 at the end of the list is so the macro can find the bounds of the array to average. I place the average of the first set of numbers at H1 then the second average calculation is placed in the same row as the second -1 just as you had in you original spreadsheet.
    I placed my module into your original spreadsheet and you had 2 -1 values - My macro places a -1 at the end of your original list and then calculates the two average calculations just as your original spreadsheet did - there are two average calculations and my macro calculates this so I am unsure what you mean by it mostly works? You do not have to add any data manually - the macro will conditionally add the -1 value at the end if not present.
    Is there a problem with adding a -1 value at the end. ?
    I uploaded another sheet today and there is 7 average calculations to make which the macro does. I am unsure what is not working. Get back to me and upload a sheet showing the errors?

    Re: Using Excel to count the amount of selections in a drop down list.


    Hi there
    I have had a look - I see you have all the lookups and they work fine - how are you handling the addition of one person to each group when a selection is made? or is this something you need to have coded as well. It can be done (getting late in Aus) so will have a look again tomorrow and get back to you then
    I need to know how you are handling once a selection is made how do you update sheet 3 with total number students in the group? or do you need a solution for that as well. Creating the dynamic drop down list is fine = depends on what else needs to happen as well. If you could let me know and I will get back to you tomorrow

    Re: Transpose/Extract data from multiple forms to another excel workbook


    How you going. I understand your a newbie...we all were so don't worry about that. I just always like people to have a go. You can never do any damage as long as you use sample data.
    Ok - to have a more flexible design as you describe is good practice. This way you can expand the database fields and dynamically populate the array in your program. The headings you use in the log sheet will need to match the forms you make so be aware of that.
    What you need to do is create an array based on the headings in the log sheet and you would use the following code to do this. Notice I have commented out the old way you made the array. The new way reads in the headings from row 1 in the log sheet and then sets this as the array you use to find the corresponding data.


    You can now add new columns to the log sheet and then in the form add in the new information to collect


    I hope this helps

    Re: Using Excel to count the amount of selections in a drop down list.


    Hi there
    I certainly is possible. You would have to create a dynamic drop down list based on the criteria you describe. It would be a lot easier to help you though if you were to upload a sample workbook. You can certainly generate only a drop down list populated with values that fit your description but without a workbook really hard to help. Put some sample data into the workbook and clearly indicate exactly what you want to do

    Re: Selecting a data range without using a loop


    What Robert meant to type was

    Code
    Option Explicit
    Sub Macro1()
        
        Dim rngMyDataRange As Range
        
    Set rngMyDataRange = Range("A2:AU" & Range("C" & Rows.Count).End(xlUp).Row)
        
    End Sub


    From what I have seen Roberts pretty much on the ball so I am sure he just missed the SET command