Posts by danerida

  • Macro Progress Bar Help

    Re: Macro Progress Bar Help


    I'm not sure what your progress bar form is not doing for you, but you can speed your code execution up a lot by avoiding .select and .copy/.paste. These functions rely use the clipboard and store data in between each step. Using .copy (destination) is much quicker.


    Try:


    Dan

  • Combine tabs from one Workbook to a single sheet of another

    Re: Combine tabs from one Workbook to a single sheet of another


    You'll need to adjust the Left statement to suit your company name. This will only work if the company name is the same length every time.

    Code
    Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 3) = "ABC" Then
    'Your code here
    Else
    'Your other code here
    End If
Next ws
End Sub


    Dan

  • Index Match links correctly to Named ranges

    Re: Index Match links correctly to Named ranges


    As an observation, you have no reference to the DATABASE worksheet for your ranges PRICES and WHEN.


    Also, your code can be made more efficient by removing the selects and references to selection:


    Code
    Columns("I:I").Select     
    Selection.AutoFilter 
    ActiveSheet.Range("I1:I" & r).AutoFilter Field:=1, Criteria1:="1" 
    Range("I1:J" & r).Copy Sheets("STATUS").Range("F1").Paste 
    Sheets("DATABASE").Range("K1:K" & r).Copy 
    Sheets("STATUS").Range("I1").Paste

  • Converting Index match formula into VBA controlled vial button

    Re: Converting Index match formula into VBA controlled vial button


    Quote from cbs9908;738168

    This works without error but doesnt actually do anything. no cells have information populated to them when I run this script, it simple loads for 1 sec then nothing. Is something else missing that actually puts it in the cells?
    Thanks,
    Cody


    Code
    ActiveSheet.Range("A1").Value =


    Is the part that puts the value into Cell A1 of the active sheet. Which sheet did you put the button on? You should change the ActiveSheet reference to Worksheets("YOURSHEETNAME").etc to be sure the value is put where you expect it to go.
    Also, you should check this part "SCMData!C[4]&SCMData!C" of the formula (SCMData!E:E&SCMData!A:A) in the original. This will not work in a Match formula. If you are trying to add a condition to the Match part, this is possible, but not done this way. Does the original formula actually return the value you are expecting?


    It would be much easier to help with a sample file that clearly demonstrates what you are trying to achieve.


    Dan

  • Passing local variable value to a global variable in a sub

    Re: Passing local variable value to a global variable in a sub


    The last three statements all need .value added to them to work i.e. Worksheets(SN).Range("AE3").value


    I'm not sure what you mean by your second point. What count are you "picking up"? Where from, and where do you want it put?


    BTW, when recording macros, there are many steps that can be removed to make your code much more efficient. Excel records every action into recorded macros, and this includes selection and activation etc. By removing these interactions with the sheet itself, you can speed things up a lot. e.g.


    Can be reduced to:

    Code
    Range("AD3").FormulaR1C1 = "PSQ"     
    Range("AE3").FormulaR1C1 = "=COUNTIF(R3C2:R500C2,""<>""&"""")" 
    Range("AD4").FormulaR1C1 = "Concur" 
    Range("AE4").FormulaR1C1 = "=COUNTIF(R3C28:R500C28,""Y"")" 
    Range("AD5").FormulaR1C1 = "Reject" 
    Range("AE5").FormulaR1C1 = "=COUNTIF(R3C28:R500C28,""N"")"


    Hope this is helpful.
    Dan

  • Index, Match formula not working

    Re: Index, Match formula not working


    This formula works. Paste it into J2 and copy down the entire column. Change the "No Match" to whatever you want shown when no match is found for the troop number.

    Code
    =IFERROR(INDEX(Sheet2!$A$1:$AF$505,MATCH(Sheet1!$E2,Sheet2!$H$1:$H$505,0),1),"No Match")


    Dan.

  • Index, Match formula not working

    Re: Index, Match formula not working



    Can you clarify your requirement? The file you sent me seems completely different to your description. There is no named range AddOn_Table or AddedOn and your worksheets all have the default names.


    What information are you trying to retrieve, where from and what are the criteria?


    Dan

  • Display message if one drop-down selected before another

    Re: Display message if one drop-down selected before another


    Depending on how your lists are generated, you could add an if statement to your second list formula i.e. IF(List1="","Please Select Something From List1", Whatever else happens to make your List2)
    That way every entry in the list will show the message.


    Dan.

  • Index, Match formula not working

    Re: Index, Match formula not working


    Quote from mfernandez;737423

    There has got to be an easier way to do this. I have been working on this for days. Now, I've got my index, match formula to extract data from one sheet to the next sheet. But instead of matching Column G (girl scout troop number) to Column V (girl scout troop number on the report with the stuff I want to extract) and then giving me the data in column V (whether or not that troop submitted a financial document). It is extracting column V based on the row number, not on the troop number in Column G.


    =INDEX(WordPress!$A$2:$CK$2288,MATCH($F2,WordPress!$G$2:$G$22288,0),COLUMN(V2))


    What I want, is for excel to look at column F of my report and compare it to column G on the financial report, then based on the troop being the same number I want it to return whatever is in Column V of the financial report.


    Your ranges in this formula have a different number of rows (2288 vs 22288).


    Try:


    Code
    =INDEX(WordPress!$A$2:$CK$2288,MATCH($F2,WordPress!$G$2:$G$2288,0),COLUMN(WordPress!V2))


    Also, for further testing, you could manually enter the values in the cells you are referring to to make sure your data is ok. If there is an issue with your data, you may need to clean that up first.


    You can PM me the link to your drop box if you want me to look at it for you.


    Dan

  • Index, Match formula not working

    Re: Index, Match formula not working


    Have you tried it without the named ranges to check if your names reference the correct ranges. Also, you could use 8 instead of COLUMN(AddOn050102!H2). I believe the ranges must all have the same number of rows for the formula to work.
    i.e. =INDEX(    Addon050102!$A$1:$H$50,MATCH($F2,Addon050102!$A$1:$A$50,0),8)


    Another possible issue could be your value in F2 vs the values in column A of 'Addon050102'. Leading and trailing spaces, and number formatting can have an effect on results with INDEX/MATCH.


    Dan

  • Problem With IF(AND(OR for combining two lists to get a combined value

    Re: Problem With IF(AND(OR for combining two lists to get a combined value


    You have left out the range references in the second part of you OR statements i.e. Or(F276="High","Moderate-High") should be Or(F276="High",F267="Moderate-High")

    Code
    =IF(AND(OR(F276="High",F276="Moderate-High"),OR(F277="Low",F277="Moderate")),"High",IF(AND(OR(F276="Low",F276="Moderate-Low"),OR(F277="Low",F277="Moderate")),"Low",IF(AND(OR(F276="Moderate-High",F276="Moderate-Low"),OR(F277="Moderate",F277="High")),"Moderate-High",IF(AND(OR(F276="Moderate-Low",F276="Moderate-High",F276="Low"),OR(F277="High",F277="Moderate",F277="Low")),"Moderate-Low",IF(AND(F276="High",F277="High"),"Very High","Please Enter Static and STABLE Scores")))))


    Dan

  • Fetch Specific Character from Cell

    Re: Fetch Specific Character from Cell


    Code
    =IF(ISERROR(SEARCH("Current",A2)),MID(A2,SEARCH("Overdue",A2)-4,11),MID(A2,SEARCH("Current",A2)-4,11))


    Assuming that your data is consistent with no spaces around the hyphens etc.
    Dan