Hi all,

Who could give me a clue on what I do wrong, I realy can't get it right
here.

I export data from Access to Excel, so far all fine. Than I format the excel
sheet a bit etc. als ok. But now I would like to write a formula to a group
of cells, and this does not work and i can't figure out what goes wrong, the
cells just apear blank.

Dim intT As Integer, stgText As String
With objActiveWorkSheet
For intT = 2 To (stgMaxRow - 5)
stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) & "X" &
Chr(34) & ";" & Chr(34) & "" & Chr(34) & ")"
'stgText = "-" & stgText
.Cells(intT, 11) = stgText
Next
End With

If I put any character in front of the string stgText it just prints the
text without trouble. Just to get the Excel formula to work seems a problem.
And strange enough I manage to to it elsewere in the sheet with
..Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" & Trim(Str(Val(stgMaxRow) - 5))
& ")"

Suggestions are highly appriciated.

Cheers,
Ludovic

Simon,

Thanks for your suggestion, but this does not go either, i get a 'Run-time
error 1004' 'Application-defined or object-defined error'. But as soon as i
put an character in front of the string (variant or sting type stgText) it
runs, just putting text in the comumn.

I have no clue what goes wrong because with the following code i have no
trouble;
.Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" & Trim(Str(Val(stgMaxRow) -
5)) & ")"
It perfectly puts the folmula in the desination cell.

Hopefully someone knows the answer here.

Cheers,
Ludovic



"Simon Lloyd" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
>
> Try this:
>
> Code:
> --------------------
> Dim intT As Integer, stgText As Variant
> With objActiveWorkSheet
> For intT = 2 To (stgMaxRow - 5)
> stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) & "X" & Chr(34)
> & ";" & Chr(34) & "" & Chr(34) & ")"
> .Cells(intT, 11).Formula = stgText
> Next
> End With
> --------------------
>
>
> Vsn;551952 Wrote:
>> Hi all,
>>
>> Who could give me a clue on what I do wrong, I realy can't get it right
>> here.
>>
>> I export data from Access to Excel, so far all fine. Than I format the
>> excel
>> sheet a bit etc. als ok. But now I would like to write a formula to a
>> group
>> of cells, and this does not work and i can't figure out what goes
>> wrong, the
>> cells just apear blank.
>>
>> >
> Code:
> --------------------
> > >
> > Dim intT As Integer, stgText As String
> > With objActiveWorkSheet
> > For intT = 2 To (stgMaxRow - 5)
> > stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) & "X" &
> > Chr(34) & ";" & Chr(34) & "" & Chr(34) & ")"
> > 'stgText = "-" & stgText
> > .Cells(intT, 11) = stgText
> > Next
> > End With
> >
> --------------------
>> >
>> If I put any character in front of the string stgText it just prints
>> the
>> text without trouble. Just to get the Excel formula to work seems a
>> problem.
>> And strange enough I manage to to it elsewere in the sheet with
>> >
> Code:
> --------------------
> > >
> > ..Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" &
> Trim(Str(Val(stgMaxRow) - 5))
> > & ")"
> >
> --------------------
>> >
>> Suggestions are highly appriciated.
>>
>> Cheers,
>> Ludovic
>
>
> --
> Simon Lloyd
>
> Regards,
> Simon Lloyd
> 'Microsoft Office Help' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.thecodecage.com/forumz/member.php?userid=1
> View this thread:
> http://www.thecodecage.com/forumz/sh...d.php?t=152093
>
> Microsoft Office Help
>

VBA is USA centric.

Write your formulas using the comma as the list separator (not the semicolon).

Option Explicit
Sub testme()

Dim intT As Long
Dim stgText As String
Dim objActiveWorksheet As Worksheet
Dim stgMaxRow As Long

Set objActiveWorksheet = ActiveSheet

stgMaxRow = 10

With objActiveWorksheet
For intT = 2 To (stgMaxRow - 5)
stgText = "=IF(F" & intT & ">1000," _
& Chr(34) & "X" & Chr(34) _
& "," & Chr(34) & "" & Chr(34) & ")"
'stgText = "-" & stgText
With .Cells(intT, 11)
.NumberFormat = "General"
.Formula = stgText
End With
Next
End With
End Sub

ps. I wouldn't use "As Integer". "As Long" turns out to be quicker for modern
pc's and can hold larger numbers.

And this structure:
Trim(Str(intT))
isn't needed.

VBA can concatenate text with numbers

pps. I explicitly made sure that the numberformat wasn't text and used the
..formula property. It can't hurt.


Vsn wrote:
>
> Hi all,
>
> Who could give me a clue on what I do wrong, I realy can't get it right
> here.
>
> I export data from Access to Excel, so far all fine. Than I format the excel
> sheet a bit etc. als ok. But now I would like to write a formula to a group
> of cells, and this does not work and i can't figure out what goes wrong, the
> cells just apear blank.
>
> Dim intT As Integer, stgText As String
> With objActiveWorkSheet
> For intT = 2 To (stgMaxRow - 5)
> stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) & "X" &
> Chr(34) & ";" & Chr(34) & "" & Chr(34) & ")"
> 'stgText = "-" & stgText
> .Cells(intT, 11) = stgText
> Next
> End With
>
> If I put any character in front of the string stgText it just prints the
> text without trouble. Just to get the Excel formula to work seems a problem.
> And strange enough I manage to to it elsewere in the sheet with
> .Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" & Trim(Str(Val(stgMaxRow) - 5))
> & ")"
>
> Suggestions are highly appriciated.
>
> Cheers,
> Ludovic

--

Dave Peterson

Thanks a LOT, it works excelent now, who could had thought about this
conversion issue! I took in your other advices as well, thanks.

I have troubles as well using a Dutch version on Excel (Office 2007) where
all the formulas have to be entered in 'local' code, what a bugger [=sum()
should be =som() and =if() as =als()]!

Cheers,
Ludovic


"Dave Peterson" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> VBA is USA centric.
>
> Write your formulas using the comma as the list separator (not the
> semicolon).
>
> Option Explicit
> Sub testme()
>
> Dim intT As Long
> Dim stgText As String
> Dim objActiveWorksheet As Worksheet
> Dim stgMaxRow As Long
>
> Set objActiveWorksheet = ActiveSheet
>
> stgMaxRow = 10
>
> With objActiveWorksheet
> For intT = 2 To (stgMaxRow - 5)
> stgText = "=IF(F" & intT & ">1000," _
> & Chr(34) & "X" & Chr(34) _
> & "," & Chr(34) & "" & Chr(34) & ")"
> 'stgText = "-" & stgText
> With .Cells(intT, 11)
> .NumberFormat = "General"
> .Formula = stgText
> End With
> Next
> End With
> End Sub
>
> ps. I wouldn't use "As Integer". "As Long" turns out to be quicker for
> modern
> pc's and can hold larger numbers.
>
> And this structure:
> Trim(Str(intT))
> isn't needed.
>
> VBA can concatenate text with numbers
>
> pps. I explicitly made sure that the numberformat wasn't text and used
> the
> .formula property. It can't hurt.
>
>
> Vsn wrote:
>>
>> Hi all,
>>
>> Who could give me a clue on what I do wrong, I realy can't get it right
>> here.
>>
>> I export data from Access to Excel, so far all fine. Than I format the
>> excel
>> sheet a bit etc. als ok. But now I would like to write a formula to a
>> group
>> of cells, and this does not work and i can't figure out what goes wrong,
>> the
>> cells just apear blank.
>>
>> Dim intT As Integer, stgText As String
>> With objActiveWorkSheet
>> For intT = 2 To (stgMaxRow - 5)
>> stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) &
>> "X" &
>> Chr(34) & ";" & Chr(34) & "" & Chr(34) & ")"
>> 'stgText = "-" & stgText
>> .Cells(intT, 11) = stgText
>> Next
>> End With
>>
>> If I put any character in front of the string stgText it just prints the
>> text without trouble. Just to get the Excel formula to work seems a
>> problem.
>> And strange enough I manage to to it elsewere in the sheet with
>> .Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" & Trim(Str(Val(stgMaxRow) -
>> 5))
>> & ")"
>>
>> Suggestions are highly appriciated.
>>
>> Cheers,
>> Ludovic
>
> --
>
> Dave Peterson

You may want to read about .formulalocal in VBA's help.

I'm sitting in the USA and have never used it with non-USA settings/language.

Vsn wrote:
>
> Thanks a LOT, it works excelent now, who could had thought about this
> conversion issue! I took in your other advices as well, thanks.
>
> I have troubles as well using a Dutch version on Excel (Office 2007) where
> all the formulas have to be entered in 'local' code, what a bugger [=sum()
> should be =som() and =if() as =als()]!
>
> Cheers,
> Ludovic
>
> "Dave Peterson" <(E-Mail Removed)> schreef in bericht
> news:(E-Mail Removed)...
> > VBA is USA centric.
> >
> > Write your formulas using the comma as the list separator (not the
> > semicolon).
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim intT As Long
> > Dim stgText As String
> > Dim objActiveWorksheet As Worksheet
> > Dim stgMaxRow As Long
> >
> > Set objActiveWorksheet = ActiveSheet
> >
> > stgMaxRow = 10
> >
> > With objActiveWorksheet
> > For intT = 2 To (stgMaxRow - 5)
> > stgText = "=IF(F" & intT & ">1000," _
> > & Chr(34) & "X" & Chr(34) _
> > & "," & Chr(34) & "" & Chr(34) & ")"
> > 'stgText = "-" & stgText
> > With .Cells(intT, 11)
> > .NumberFormat = "General"
> > .Formula = stgText
> > End With
> > Next
> > End With
> > End Sub
> >
> > ps. I wouldn't use "As Integer". "As Long" turns out to be quicker for
> > modern
> > pc's and can hold larger numbers.
> >
> > And this structure:
> > Trim(Str(intT))
> > isn't needed.
> >
> > VBA can concatenate text with numbers
> >
> > pps. I explicitly made sure that the numberformat wasn't text and used
> > the
> > .formula property. It can't hurt.
> >
> >
> > Vsn wrote:
> >>
> >> Hi all,
> >>
> >> Who could give me a clue on what I do wrong, I realy can't get it right
> >> here.
> >>
> >> I export data from Access to Excel, so far all fine. Than I format the
> >> excel
> >> sheet a bit etc. als ok. But now I would like to write a formula to a
> >> group
> >> of cells, and this does not work and i can't figure out what goes wrong,
> >> the
> >> cells just apear blank.
> >>
> >> Dim intT As Integer, stgText As String
> >> With objActiveWorkSheet
> >> For intT = 2 To (stgMaxRow - 5)
> >> stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) &
> >> "X" &
> >> Chr(34) & ";" & Chr(34) & "" & Chr(34) & ")"
> >> 'stgText = "-" & stgText
> >> .Cells(intT, 11) = stgText
> >> Next
> >> End With
> >>
> >> If I put any character in front of the string stgText it just prints the
> >> text without trouble. Just to get the Excel formula to work seems a
> >> problem.
> >> And strange enough I manage to to it elsewere in the sheet with
> >> .Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" & Trim(Str(Val(stgMaxRow) -
> >> 5))
> >> & ")"
> >>
> >> Suggestions are highly appriciated.
> >>
> >> Cheers,
> >> Ludovic
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Dave,

This hole '.formulalocal' does not appear in my help? I would nearly give an
arm and a leg to be able to use my Dutch version(s) with the regular english
formula coding. I just don't understand it is not an option since surly the
english is the 'native' code.

Regards,
Ludovic

"Dave Peterson" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> You may want to read about .formulalocal in VBA's help.
>
> I'm sitting in the USA and have never used it with non-USA
> settings/language.
>
> Vsn wrote:
>>
>> Thanks a LOT, it works excelent now, who could had thought about this
>> conversion issue! I took in your other advices as well, thanks.
>>
>> I have troubles as well using a Dutch version on Excel (Office 2007)
>> where
>> all the formulas have to be entered in 'local' code, what a bugger
>> [=sum()
>> should be =som() and =if() as =als()]!
>>
>> Cheers,
>> Ludovic
>>
>> "Dave Peterson" <(E-Mail Removed)> schreef in bericht
>> news:(E-Mail Removed)...
>> > VBA is USA centric.
>> >
>> > Write your formulas using the comma as the list separator (not the
>> > semicolon).
>> >
>> > Option Explicit
>> > Sub testme()
>> >
>> > Dim intT As Long
>> > Dim stgText As String
>> > Dim objActiveWorksheet As Worksheet
>> > Dim stgMaxRow As Long
>> >
>> > Set objActiveWorksheet = ActiveSheet
>> >
>> > stgMaxRow = 10
>> >
>> > With objActiveWorksheet
>> > For intT = 2 To (stgMaxRow - 5)
>> > stgText = "=IF(F" & intT & ">1000," _
>> > & Chr(34) & "X" & Chr(34) _
>> > & "," & Chr(34) & "" & Chr(34) & ")"
>> > 'stgText = "-" & stgText
>> > With .Cells(intT, 11)
>> > .NumberFormat = "General"
>> > .Formula = stgText
>> > End With
>> > Next
>> > End With
>> > End Sub
>> >
>> > ps. I wouldn't use "As Integer". "As Long" turns out to be quicker
>> > for
>> > modern
>> > pc's and can hold larger numbers.
>> >
>> > And this structure:
>> > Trim(Str(intT))
>> > isn't needed.
>> >
>> > VBA can concatenate text with numbers
>> >
>> > pps. I explicitly made sure that the numberformat wasn't text and used
>> > the
>> > .formula property. It can't hurt.
>> >
>> >
>> > Vsn wrote:
>> >>
>> >> Hi all,
>> >>
>> >> Who could give me a clue on what I do wrong, I realy can't get it
>> >> right
>> >> here.
>> >>
>> >> I export data from Access to Excel, so far all fine. Than I format the
>> >> excel
>> >> sheet a bit etc. als ok. But now I would like to write a formula to a
>> >> group
>> >> of cells, and this does not work and i can't figure out what goes
>> >> wrong,
>> >> the
>> >> cells just apear blank.
>> >>
>> >> Dim intT As Integer, stgText As String
>> >> With objActiveWorkSheet
>> >> For intT = 2 To (stgMaxRow - 5)
>> >> stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) &
>> >> "X" &
>> >> Chr(34) & ";" & Chr(34) & "" & Chr(34) & ")"
>> >> 'stgText = "-" & stgText
>> >> .Cells(intT, 11) = stgText
>> >> Next
>> >> End With
>> >>
>> >> If I put any character in front of the string stgText it just prints
>> >> the
>> >> text without trouble. Just to get the Excel formula to work seems a
>> >> problem.
>> >> And strange enough I manage to to it elsewere in the sheet with
>> >> .Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" &
>> >> Trim(Str(Val(stgMaxRow) -
>> >> 5))
>> >> & ")"
>> >>
>> >> Suggestions are highly appriciated.
>> >>
>> >> Cheers,
>> >> Ludovic
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

Were you looking in VBA's help or Excel's help?

Make sure you were in the VBE.

Vsn wrote:
>
> Dave,
>
> This hole '.formulalocal' does not appear in my help? I would nearly give an
> arm and a leg to be able to use my Dutch version(s) with the regular english
> formula coding. I just don't understand it is not an option since surly the
> english is the 'native' code.
>
> Regards,
> Ludovic
>
> "Dave Peterson" <(E-Mail Removed)> schreef in bericht
> news:(E-Mail Removed)...
> > You may want to read about .formulalocal in VBA's help.
> >
> > I'm sitting in the USA and have never used it with non-USA
> > settings/language.
> >
> > Vsn wrote:
> >>
> >> Thanks a LOT, it works excelent now, who could had thought about this
> >> conversion issue! I took in your other advices as well, thanks.
> >>
> >> I have troubles as well using a Dutch version on Excel (Office 2007)
> >> where
> >> all the formulas have to be entered in 'local' code, what a bugger
> >> [=sum()
> >> should be =som() and =if() as =als()]!
> >>
> >> Cheers,
> >> Ludovic
> >>
> >> "Dave Peterson" <(E-Mail Removed)> schreef in bericht
> >> news:(E-Mail Removed)...
> >> > VBA is USA centric.
> >> >
> >> > Write your formulas using the comma as the list separator (not the
> >> > semicolon).
> >> >
> >> > Option Explicit
> >> > Sub testme()
> >> >
> >> > Dim intT As Long
> >> > Dim stgText As String
> >> > Dim objActiveWorksheet As Worksheet
> >> > Dim stgMaxRow As Long
> >> >
> >> > Set objActiveWorksheet = ActiveSheet
> >> >
> >> > stgMaxRow = 10
> >> >
> >> > With objActiveWorksheet
> >> > For intT = 2 To (stgMaxRow - 5)
> >> > stgText = "=IF(F" & intT & ">1000," _
> >> > & Chr(34) & "X" & Chr(34) _
> >> > & "," & Chr(34) & "" & Chr(34) & ")"
> >> > 'stgText = "-" & stgText
> >> > With .Cells(intT, 11)
> >> > .NumberFormat = "General"
> >> > .Formula = stgText
> >> > End With
> >> > Next
> >> > End With
> >> > End Sub
> >> >
> >> > ps. I wouldn't use "As Integer". "As Long" turns out to be quicker
> >> > for
> >> > modern
> >> > pc's and can hold larger numbers.
> >> >
> >> > And this structure:
> >> > Trim(Str(intT))
> >> > isn't needed.
> >> >
> >> > VBA can concatenate text with numbers
> >> >
> >> > pps. I explicitly made sure that the numberformat wasn't text and used
> >> > the
> >> > .formula property. It can't hurt.
> >> >
> >> >
> >> > Vsn wrote:
> >> >>
> >> >> Hi all,
> >> >>
> >> >> Who could give me a clue on what I do wrong, I realy can't get it
> >> >> right
> >> >> here.
> >> >>
> >> >> I export data from Access to Excel, so far all fine. Than I format the
> >> >> excel
> >> >> sheet a bit etc. als ok. But now I would like to write a formula to a
> >> >> group
> >> >> of cells, and this does not work and i can't figure out what goes
> >> >> wrong,
> >> >> the
> >> >> cells just apear blank.
> >> >>
> >> >> Dim intT As Integer, stgText As String
> >> >> With objActiveWorkSheet
> >> >> For intT = 2 To (stgMaxRow - 5)
> >> >> stgText = "=IF(F" & Trim(Str(intT)) & ">1000;" & Chr(34) &
> >> >> "X" &
> >> >> Chr(34) & ";" & Chr(34) & "" & Chr(34) & ")"
> >> >> 'stgText = "-" & stgText
> >> >> .Cells(intT, 11) = stgText
> >> >> Next
> >> >> End With
> >> >>
> >> >> If I put any character in front of the string stgText it just prints
> >> >> the
> >> >> text without trouble. Just to get the Excel formula to work seems a
> >> >> problem.
> >> >> And strange enough I manage to to it elsewere in the sheet with
> >> >> .Cells(Val(stgMaxRow) - 3, 6) = "=sum(F2:F" &
> >> >> Trim(Str(Val(stgMaxRow) -
> >> >> 5))
> >> >> & ")"
> >> >>
> >> >> Suggestions are highly appriciated.
> >> >>
> >> >> Cheers,
> >> >> Ludovic
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson