Who can tell me where I go wrong?

Henrootje · 11-07-2005, 03:30 PM

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

Don Guillett · 11-07-2005, 04:10 PM

wouldn't something like this be cleaner WITHOUT selections?

Sub makeformulaonsheet()
with Sheets("sheet6")
..Range("i1").End(xlDown).offset(1).Formula = "=i1+i2"
end with
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Henrootje" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Henrootje · 11-07-2005, 04:20 PM

It surely is! Thanx a lot!

Don Guillett · 11-07-2005, 04:25 PM

glad to help. Post back with your final code.

--
Don Guillett
SalesAid Software
[email protected]
"Henrootje" <[email protected]> wrote in message
news:[email protected]...
> It surely is! Thanx a lot!
>

Henrootje · 11-07-2005, 04:45 PM

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)

Don Guillett · 11-07-2005, 07:30 PM

I can't figure out all of your formulas but you missed my point. You can
change your code to avoid selections and make it easier.

Sub makeformulaonsheet()
with Sheets("hoofdblad")
'.Range("i1").End(xlDown).offset(1).Formula = "=i1+i2"
..Range("B2").End(xlDown).Offset(1).Formula = "=" & NieuwArtikelNr & "!$A$3"
..Range("C2").End(xlDown).Offset(1).Formula = you fill in the rest
..Range("D2").End(xlDown).Offset(1).Formula = FormuleD
..Range("E2").End(xlDown).Offset(1).Formula = FormuleE
..Range("F2").End(xlDown).Offset(1).Formula = FormuleF
..Range("G2").End(xlDown).Offset(1).Formula = FormuleG
..Range("H2").End(xlDown).Offset(1).Formula = FormuleH
..Range("I2").End(xlDown).Offset(1).Formula = FormuleI
..Range("J2").End(xlDown).Offset(1).Formula = "=" & NieuwArtikelNr &
"!$D$3+365"

end with
End Sub

You don't say what this is? NieuwArtikelNr. If a sheet name, try
"=NieuwArtikelNr!$A$3"
instead of
"=" & NieuwArtikelNr & "!$A$3"

--
Don Guillett
SalesAid Software
[email protected]
"Henrootje" <[email protected]> wrote in message
news:[email protected]...
> 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)
>