| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range empty?
Hi There,
How comes Range = Nothing? I would like the message box to show something like: F125 Thanks, Sige Sub Show_Usedrange() Dim lngLastRow As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell End With End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
Change: MsgBox rLastRowCell to: MsgBox rLastRowCell.Address --- Regards, Norman "Sige" wrote in message oups.com... Hi There, How comes Range = Nothing? I would like the message box to show something like: F125 Thanks, Sige Sub Show_Usedrange() Dim lngLastRow As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell End With End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Thx Norman!!!
PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Thx Norman!!!
PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
PS: Were you able to trap the constants After the "*"-sign in my previous thread? Yes see my reponse in that thread and the conversation with Jan Karel Pieterse: In summary: Change: arr = Array("/", "~*", "+", "-", "", "<", "=", "^") to: arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") --- Regards, Norman "SIGE" wrote in message ... Thx Norman!!! PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
Yes - see response in that thread. --- Regards, Norman "SIGE" wrote in message ... Thx Norman!!! PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Any reason why then
.Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" Returns empty? Brgds, Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address .Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" End With End Sub "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
Try: ..Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell. _ Address(0, 0) & "="""";"""";$D$1)" Note the doubled apostrophes. --- Regards, Norman "SIGE" wrote in message .. . Any reason why then Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" Returns empty? Brgds, Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" End With End Sub "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Norman,
You are my today's hero ... !!! Though this one does not write the formula... It beats me. Sige |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Siege,
Please enter the formula in the worksheet manually and copy the resultant formula into a reply. --- Regards, Norman "Sige" wrote in message ups.com... Hi Norman, You are my today's hero ... !!! Though this one does not write the formula... It beats me. Sige |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Norman,
You mean that I just have the show you what kind of formula should be written ..? =IF(F12="";"";$D$1) HTH Sige "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige
I ran your sub on a test sheet using: Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" and successfully entered your required formula. Your local settings require the use of semi-colons whereas the corresponding US/UK character is a a comma. Therefore, for my test purposes, I replaced the two IF formula semi-colons with commas. Of course, if the relevant column F cell is empty, your column O formula will return an empty string and, although present, will not be visible --- Regards, Norman "SIGE" wrote in message ... Hi Norman, You mean that I just have the show you what kind of formula should be written ..? =IF(F12="";"";$D$1) HTH Sige "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Norman,
I cannot figure out what is wrong ...I take underneath sub ...plug it into a new workbook...trow some numbers in it..... run it ...&: I can write the "=$D$1"-functions but my IF-functions do not appear anywhere! But why? God knows ... I will dream about it tonight! Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address .Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 4).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Font.Name = "Arial" .Cells(lngLastRow + 1, 7).Formula = "=$D$1" .Cells(lngLastRow + 1, 7).Font.Name = "Arial" .Cells(lngLastRow + 1, 8).Formula = "=$D$1" .Cells(lngLastRow + 1, 8).Font.Name = "Arial" .Cells(lngLastRow + 1, 9).Formula = "" .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" End With End Sub |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
I ran your Sub Check_Usedrange() procedure and the required three IF formulas were correctly inserted. The only change that I made to your sub was to replace the semi-colons in the IF forrmula expressions with the US/UK standard commas. I assumed from your use of semi-colons that your local settings required semi-colons, as would be the case if, for example, you were using a French or Italian version of Excel. However, this would also require you to change IF to SE or SI for those versions. I wonder, therefore, if you have not simply used semi-colons in error. So what happens if, like me, you change: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" to: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""","""",$D$1)" And, to resolve confusion, what are your version / country settings? --- Regards, Norman "Sige" wrote in message oups.com... Hi Norman, I cannot figure out what is wrong ...I take underneath sub ...plug it into a new workbook...trow some numbers in it..... run it ...&: I can write the "=$D$1"-functions but my IF-functions do not appear anywhere! But why? God knows ... I will dream about it tonight! Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address .Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 4).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Font.Name = "Arial" .Cells(lngLastRow + 1, 7).Formula = "=$D$1" .Cells(lngLastRow + 1, 7).Font.Name = "Arial" .Cells(lngLastRow + 1, 8).Formula = "=$D$1" .Cells(lngLastRow + 1, 8).Font.Name = "Arial" .Cells(lngLastRow + 1, 9).Formula = "" .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" End With End Sub |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
Of course, the semi-colons may be correct with the 'IF' needing to be changed to SE, SI, WENN, ALS...! --- Regards, Norman "Norman Jones" wrote in message ... Hi Sige, I ran your Sub Check_Usedrange() procedure and the required three IF formulas were correctly inserted. The only change that I made to your sub was to replace the semi-colons in the IF forrmula expressions with the US/UK standard commas. I assumed from your use of semi-colons that your local settings required semi-colons, as would be the case if, for example, you were using a French or Italian version of Excel. However, this would also require you to change IF to SE or SI for those versions. I wonder, therefore, if you have not simply used semi-colons in error. So what happens if, like me, you change: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" to: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""","""",$D$1)" And, to resolve confusion, what are your version / country settings? --- Regards, Norman "Sige" wrote in message oups.com... Hi Norman, I cannot figure out what is wrong ...I take underneath sub ...plug it into a new workbook...trow some numbers in it..... run it ...&: I can write the "=$D$1"-functions but my IF-functions do not appear anywhere! But why? God knows ... I will dream about it tonight! Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address .Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 4).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Font.Name = "Arial" .Cells(lngLastRow + 1, 7).Formula = "=$D$1" .Cells(lngLastRow + 1, 7).Font.Name = "Arial" .Cells(lngLastRow + 1, 8).Formula = "=$D$1" .Cells(lngLastRow + 1, 8).Font.Name = "Arial" .Cells(lngLastRow + 1, 9).Formula = "" .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" End With End Sub |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
I think that if you're writing the formulas through code, then you use the
English versions of the functions and commas for the list separators. It'll be converted to local settings when it hits the worksheet. Using .formulalocal would require those changes, though. Norman Jones wrote: Hi Sige, Of course, the semi-colons may be correct with the 'IF' needing to be changed to SE, SI, WENN, ALS...! --- Regards, Norman "Norman Jones" wrote in message ... Hi Sige, I ran your Sub Check_Usedrange() procedure and the required three IF formulas were correctly inserted. The only change that I made to your sub was to replace the semi-colons in the IF forrmula expressions with the US/UK standard commas. I assumed from your use of semi-colons that your local settings required semi-colons, as would be the case if, for example, you were using a French or Italian version of Excel. However, this would also require you to change IF to SE or SI for those versions. I wonder, therefore, if you have not simply used semi-colons in error. So what happens if, like me, you change: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" to: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""","""",$D$1)" And, to resolve confusion, what are your version / country settings? --- Regards, Norman "Sige" wrote in message oups.com... Hi Norman, I cannot figure out what is wrong ...I take underneath sub ...plug it into a new workbook...trow some numbers in it..... run it ...&: I can write the "=$D$1"-functions but my IF-functions do not appear anywhere! But why? God knows ... I will dream about it tonight! Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address .Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 4).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Font.Name = "Arial" .Cells(lngLastRow + 1, 7).Formula = "=$D$1" .Cells(lngLastRow + 1, 7).Font.Name = "Arial" .Cells(lngLastRow + 1, 8).Formula = "=$D$1" .Cells(lngLastRow + 1, 8).Font.Name = "Arial" .Cells(lngLastRow + 1, 9).Formula = "" .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" End With End Sub -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Dave,
Thank you! That was something I once knew and had forgotten! It also confirms my suggestion to Sige that he should try the comma adaptation, so thnaks again. --- Regards, Norman "Dave Peterson" wrote in message ... I think that if you're writing the formulas through code, then you use the English versions of the functions and commas for the list separators. It'll be converted to local settings when it hits the worksheet. Using .formulalocal would require those changes, though. Norman Jones wrote: Hi Sige, Of course, the semi-colons may be correct with the 'IF' needing to be changed to SE, SI, WENN, ALS...! --- Regards, Norman "Norman Jones" wrote in message ... Hi Sige, I ran your Sub Check_Usedrange() procedure and the required three IF formulas were correctly inserted. The only change that I made to your sub was to replace the semi-colons in the IF forrmula expressions with the US/UK standard commas. I assumed from your use of semi-colons that your local settings required semi-colons, as would be the case if, for example, you were using a French or Italian version of Excel. However, this would also require you to change IF to SE or SI for those versions. I wonder, therefore, if you have not simply used semi-colons in error. So what happens if, like me, you change: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" to: .Cells(lngLastRow + 1, 15).Formula = _ "=IF(" & rLastRowCell.Address(0, 0) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 16).Formula = _ "=IF(" & rLastRowCell.Address(0, 1) & "="""","""",$D$1)" .Cells(lngLastRow + 1, 17).Formula = _ "=IF(" & rLastRowCell.Address(0, 2) & "="""","""",$D$1)" And, to resolve confusion, what are your version / country settings? --- Regards, Norman "Sige" wrote in message oups.com... Hi Norman, I cannot figure out what is wrong ...I take underneath sub ...plug it into a new workbook...trow some numbers in it..... run it ...&: I can write the "=$D$1"-functions but my IF-functions do not appear anywhere! But why? God knows ... I will dream about it tonight! Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address .Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 4).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Formula = "=$D$1" .Cells(lngLastRow + 1, 6).Font.Name = "Arial" .Cells(lngLastRow + 1, 7).Formula = "=$D$1" .Cells(lngLastRow + 1, 7).Font.Name = "Arial" .Cells(lngLastRow + 1, 8).Formula = "=$D$1" .Cells(lngLastRow + 1, 8).Font.Name = "Arial" .Cells(lngLastRow + 1, 9).Formula = "" .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 16).Formula = "=IF(" & rLastRowCell.Address(0, 1) & "="""";"""";$D$1)" .Cells(lngLastRow + 1, 17).Formula = "=IF(" & rLastRowCell.Address(0, 2) & "="""";"""";$D$1)" End With End Sub -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
And GOD spoke!
It works ... with the commas instead of the semi-colons in my code! I think that if you're writing the formulas through code, then you use the English versions of the functions and commas for the list separators. It'll be converted to local settings when it hits the worksheet. My commas get converted to Semi-colons!!! FYI: I have an english XL97 SR-2 but my country settings are Dutch(Belgium) Thx thx thx So happy :o)))) Sige PS: If have an outline issue coming up ...if interested, it will be in a next thread |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Actually, this comes up every so often. I just remembered previous posts (and a
simple test after changing my list separator). Sige wrote: And GOD spoke! It works ... with the commas instead of the semi-colons in my code! I think that if you're writing the formulas through code, then you use the English versions of the functions and commas for the list separators. It'll be converted to local settings when it hits the worksheet. My commas get converted to Semi-colons!!! FYI: I have an english XL97 SR-2 but my country settings are Dutch(Belgium) Thx thx thx So happy :o)))) Sige PS: If have an outline issue coming up ...if interested, it will be in a next thread -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| empty autofiltered range | Excel Programming | |||
| why is range empty? | Excel Discussion (Misc queries) | |||
| why is range empty? | Excel Programming | |||
| sum next two non-empty cells in a range | Excel Worksheet Functions | |||
| determinate if a range is empty | Excel Programming | |||
Linear Mode
