Posts by busybee_1_in

    I was able to resolve the issue by modifying the code. I now use


    Code
    1. rng1.Formula = Application.ConvertFormula(rng1.Formula, 1, 1)


    I removed the final parameter(ToAbsolute) in my function which makes it dynamic. I now get the correct output in my workbook.

    I created a new workbook and applied the formula and the above code on it. It does not give me errors. I cannot attach my original workbook as my data is confidential.


    My code is part of a Macro that uses Autofilter and Delete to delete rows with value "0" in Column A.


    How to Delete Rows with Range.AutoFilter | Dan Wagner Co


    My workbook is populated with reference formulas. My Macro was interpreting rows with different values as "0" and deleting them. Applying Application.ConvertFormula on all the cells in Column A resolves this error.

    I have the following formula in cell A3 of Sheet1.


    =CONCATENATE("For the ",IF(MONTH('Sheet2'!J9)=12,"Twelve",IF(MONTH('Sheet2'!J9)=9,"Nine","Six"))," Months Ended ",'Sheet2'!J9)


    Entering this formula gives the following output: For the Twelve months Ended December 31, 2020.


    However, when I use the Application.ConvertFormula function, cell A3 is converted to #VALUE!.


    Code
    1. Dim rng1 As Range
    2. Dim ws1 As Worksheet
    3. Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    4. Set rng1 = ws1.Range("A3")
    5. If rng1.HasFormula Then
    6. rng1.Formula = Application.ConvertFormula(rng1.Formula, 1, 1, 1)
    7. End If


    Can you help me figure out why do I get #VALUE! as output?

    I have the following Macro that is activated when clicking a button.


    However, when I open a worksheet and click a Button assigned this Macro I get a runtime 1004 error on the rowList.Add sShape.TopLeftCell.Row + j line. This error disappears when I click the button the second time.


    Any suggestions on how to resolve this?

    I am trying to utilize the Range.Insert method to insert new rows below my table in Range("G17:K17") in the "Output Sheet" worksheet of my attached file. The code in InsertRows function in Module1 is as follows.


    Code
    1. Set ws1 = ThisWorkbook.Worksheets("Output Sheet")
    2. Set rng1 = ws1.Range("G17:K17")
    3. rng1.Insert


    However, executing the above code affects row 22 in Output Sheet and it is no longer of contiguous color. Is it possible to shift cells down in the above range while keeping row 22 intact?


    File1.xlsm

    I am trying to write an Excel VBA Macro that copies the contents of 2 worksheets and pastes them in an Output sheet vertically aligned below each other.


    In my attached file the Macro1 I have created first creates a header in "Output Sheet" with the name of the input sheet("Worksheet 1" and "Worksheet 2").

    It then locates the first row below the header of the input sheets. It determines the first column, first row and last row of the 2 worksheets in a loop. It then increases the row height and column width in the output range if necessary. It then attempts to copy the contents to "Output sheet".


    The Macro can be executed by pressing the Execute Macro button on Sheet4. The Output Sheet must be deleted before executing the Macro.


    However, the contents(including the buttons) are not being pasted correctly.


    Any suggestions on how to resolve this?


    File1.xlsm

    I managed to create the dependent drop down list by applying the following formula in Cell F2 of Sheet1.


    =SORT(UNIQUE(FILTER(List2,(List2<>"")*(List1=Sheet2!$B$4))))


    This creates a unique list of "Dependent List" values filtered based on the selection of the 1st dropdown list.


    and then refer to these values by applying the following formula for the validation list in cell E4 of Sheet2.


    =Sheet1!$F$2#.


    I would now like to output the contents of Col C in Sheet1 to Cell B8 of Sheet2. I typed the following formula in cell G2 of Sheet1 to get a unique list of "Final Output" values.


    =UNIQUE(FILTER(List3,(List3<>"")*(List2=Sheet2!$E$4))).


    However, this formula does not work with merged cells in "Dependent Values". Could you suggest a suitable formula that would work with merged cells?


    The new workbook is attached below.


    Testing2.xlsm

    I have attached a workbook modeled on my data. In Sheet1, Column A has the contents of my 1st dropdown list, Column B has the contents of my dependent dropdown list.


    In Sheet2, I have placed the 1st dropdown list in Cell B4, I would like to create the dependent dropdown list in Cell E4.


    My final objective is to dynamically output the contents of Col C in Sheet 1 based on the selections in the 2 dropdown lists.


    Testing2.xlsx

    I am attempting to create a dependent drop down list. I have 2 named ranges in the attached file created with this formula.


    List1=OFFSET(Sheet1!$A$2,0,0,(COUNTBLANK(Sheet1!$A:$A)+COUNTA(Sheet1!$A:$A)-1),1)

    List2=OFFSET(Sheet1!$B$2,0,0,(COUNTBLANK(Sheet1!$B:$B)+COUNTA(Sheet1!$B:$B)-1),1)


    I have a drop down list in Cell B4 of Sheet2 consisting of unique values in Column A of Sheet1. I am attempting to create a dependent drop down list of Column B values in Sheet1 in cell E4 of Sheet2. The formula I have is as follows.


    =IFERROR(INDEX(List2,SMALL(IF($B$4=List1,ROW(List2)-ROW(Sheet1!$A$2)+1,ROW(1:1))),"")


    However, I am not able to create the dependent list. Any suggestions on how to resolve this?


    Testing1.xlsx

    Thanks for the help.


    The Excel file I posted was only a Testing file. My task involves getting a unique list from a column with merged cells. I utilized the following formula to get a unique list.


    =UNIQUE(FILTER(OFFSET(A2,0,0,(COUNTBLANK(A:A)+COUNTA(A:A)-1),1),OFFSET(C2,0,0,(COUNTBLANK(A:A)+COUNTA(A:A)-1),1)<>""))

    I have an Excel file with data in Column A. My attached file has data from A2:A6. I want to create a drop down list on Cell B2 of Sheet2. I would like to create a list that will be populated with the data in the Used Range in Sheet1's Column A i.e. if this column has additional data in the future it should change dynamically.


    I tried to apply a Formula for my Validation Object I found on the following website but it does not work.


    Excel Data Validation -- Dependent Lists With INDEX (contextures.com)


    =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))


    Is it possible to create such a drop down list with a Formula?

    Files

    • Testing1.xlsx

      (16.37 kB, downloaded 29 times, last: )

    I am trying to create a multiline string and print it out with the following code.


    The output is attached below.



    How do I create a string where the text is vertically aligned so that Testing3 is precisely below Testing2?

    Is there a suitable formula for the number of spaces before Testing3?

    I am getting a runtime error 1004 on the following line of code. I am attempting to add a date validation to Cell B4 to only allow dates after 1/1/1000.


    Code
    1. With Range("B4").Validation
    2. .Delete 
    3. .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlGreaterEqual, Formula1:="1/1/1000" 
    4. .ErrorMessage = "You must enter a date value. Format:mm/dd/yyyy" 
    5. End With


    Any suggestions on how to resolve this?

    The default direction was Left-to-Right earlier but Cell A1 was in the upper right corner. I just opened Excel again and cell A1 is on the left. However the default direction is now Right-to-Left. I am not sure why Excel is working this way.