Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Who can tell me where I go wrong?
Who can tell me what is wrong with the following code?
NieuwArtikelNr is a worksheet name that functions ok in other code FormuleC and FormuleE lock up Formule G and Formule H give #NAME? but when I select the field and give an enter (above in the formuladisplayingthingie) it works like a charm. I suspect it has to do with the way I enter the formula's into the cell?? (ActiveCell.Formula = FormuleC) Anybody has suggestions??????? Dim FormuleB As String Dim FormuleC As String Dim FormuleD As String Dim FormuleE As String Dim FormuleF As String Dim FormuleG As String Dim FormuleH As String Dim FormuleI As String Dim FormuleJ As String FormuleB = "=" & NieuwArtikelNr & "!$A$3" FormuleC = "=ALS(AANTALARG('" & NieuwArtikelNr & "'!$A$9:$A$50)<=AANTALARG(" & NieuwArtikelNr & "!$D$9:$D$50);" & "Ja;" & "Nee)" FormuleD = "=" & NieuwArtikelNr & "!$D$10" FormuleE = "=ALS(AANTALARG('" & NieuwArtikelNr & "'!$A$9:$A$50)<=AANTALARG('" & NieuwArtikelNr & "'!$D$9:$D$50);'Ja';'Nee')" FormuleF = "NietGevuldInOrigineel" FormuleG = "=AANTALARG(" & NieuwArtikelNr & "!$C$9:$C$50=CHM)" FormuleH = "=AANTALARG(" & NieuwArtikelNr & "!$A$9:$A$50)" FormuleI = "=" & NieuwArtikelNr & "!$D$3" FormuleJ = FormuleI & "+ 365" 'ArtikelOmschrijving in B Sheets("hoofdblad").Select Range("B2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleB 'Beschikbaar in C Sheets("hoofdblad").Select Range("C2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleC 'Beschikbaar in D Sheets("hoofdblad").Select Range("D2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleD 'Beschikbaar in E Sheets("hoofdblad").Select Range("E2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleE 'Beschikbaar in F Sheets("hoofdblad").Select Range("F2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleF 'Beschikbaar in G Sheets("hoofdblad").Select Range("G2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleG 'Beschikbaar in H Sheets("hoofdblad").Select Range("H2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleH 'Beschikbaar in I Sheets("hoofdblad").Select Range("I2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleI 'Beschikbaar in J Sheets("hoofdblad").Select Range("J2").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = FormuleJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Who can tell me where I go wrong?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Who can tell me where I go wrong?
It surely is! Thanx a lot!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Who can tell me where I go wrong?
glad to help. Post back with your final code.
-- Don Guillett SalesAid Software "Henrootje" wrote in message oups.com... It surely is! Thanx a lot! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Who can tell me where I go wrong?
Changed code:
Dim FormuleB As String Dim FormuleC As String Dim FormuleD As String Dim FormuleE As String Dim FormuleF As String Dim FormuleG As String Dim FormuleH As String Dim FormuleI As String Dim FormuleJ As String FormuleB = "=" & NieuwArtikelNr & "!$A$3" FormuleC = "=ALS(AANTALARG(" & NieuwArtikelNr & "!$A$9:$A$50)<=AANTALARG(" & NieuwArtikelNr & "!$D$9:$D$50);""Ja"";""Nee"")" FormuleD = "=" & NieuwArtikelNr & "!$D$10" FormuleE = "=ALS(AANTALARG(" & NieuwArtikelNr & "!$A$9:$A$50)<=AANTALARG(" & NieuwArtikelNr & "!$D$9:$D$50);""Ja"";""Nee"")" FormuleF = "_" FormuleG = "=COUNTA(" & NieuwArtikelNr & "!$C$9:$C$50=CHM)" FormuleH = "=COUNTA(" & NieuwArtikelNr & "!$A$9:$A$50)" FormuleI = "=" & NieuwArtikelNr & "!$D$3" FormuleJ = FormuleI & "+ 365" Sheets("hoofdblad").Select Range("B2").End(xlDown).Offset(1, 0).Formula = FormuleB Range("C2").End(xlDown).Offset(1, 0).Formula = FormuleC Range("D2").End(xlDown).Offset(1, 0).Formula = FormuleD Range("E2").End(xlDown).Offset(1, 0).Formula = FormuleE Range("F2").End(xlDown).Offset(1, 0).Formula = FormuleF Range("G2").End(xlDown).Offset(1, 0).Formula = FormuleG Range("H2").End(xlDown).Offset(1, 0).Formula = FormuleH Range("I2").End(xlDown).Offset(1, 0).Formula = FormuleI Range("J2").End(xlDown).Offset(1, 0).Formula = FormuleJ Now Formule G = H work great but C and E do not. The trick I used in G=H, using the english expressions, did not work in C+E :( If I take out the '=' out of formula C and E I have nice text in my cells. If I manually then put the '=' in the field it works like a charm........... Suggestions? TIA!!! (Forgot that in first post, sorry) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
i did something wrong | Excel Discussion (Misc queries) | |||
What is wrong with this | Excel Discussion (Misc queries) | |||
What is wrong? | Excel Programming | |||
Where am I going wrong with this? | Excel Programming |