I was able to resolve the issue by modifying the code. I now use
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!.
Dim rng1 As Range
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set rng1 = ws1.Range("A3")
If rng1.HasFormula Then
rng1.Formula = Application.ConvertFormula(rng1.Formula, 1, 1, 1)
End IfCan you help me figure out why do I get #VALUE! as output?
I have the following Macro that is activated when clicking a button.
Sub ClickButton()
Dim sShape As Shape
Dim rowList As Object
Set rowList = CreateObject("System.Collections.ArrayList")
For Each sShape In ActiveSheet.Shapes
For j = 0 To 3
rowList.Add sShape.TopLeftCell.Row + j
Next j
rowList.Add sShape.BottomRightCell.Row
Next sShape
End SubHowever, 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?
Thanks for the solution.
In the attached Youtube video, the presenter has selected a range of data and dragged it a few rows below. Is it possible to simulate this operation in Excel VBA?
How to Shift Everything Down in Excel : Using Microsoft Excel - YouTube
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.
Set ws1 = ThisWorkbook.Worksheets("Output Sheet")
Set rng1 = ws1.Range("G17:K17")
rng1.InsertHowever, 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?
I have resolved the error. I made a mistake in the following line of code.
Set rng1 = ws1.Range(firstCol & firstRow & ":" & lastCol & lastRow)
I put in lastColumn instead of lastRow.
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?
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.
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.
The EE_Dependant_Dropdowns.xlsx file has the headers of the table as the contents of the first drop down list. My file has the contents of the 2 drop down lists adjacent to each other in the same row. I cannot transpose my data from rows to columns.
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?
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?
I am trying to create a multiline string and print it out with the following code.
Function OutputText()
Dim string1 As String
string1 = "Testing1" & Space(4) & "Testing2"
string1 = string1 & vbNewLine & Space(12) & "Testing3"
MsgBox (string1)
End FunctionThe 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?
Thank you. I have now set Formula1 to be 01/01/1900
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.
With Range("B4").Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlGreaterEqual, Formula1:="1/1/1000"
.ErrorMessage = "You must enter a date value. Format:mm/dd/yyyy"
End WithAny suggestions on how to resolve this?
The display setting for Sheet1 was set from 'right to left'. Thank you for the solution.
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.