What do I do wrong [Excuses pushing the wrong key combination earlier]

[Vsn]

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 Lloyd]

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
--------------------

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)

 

[Vsn]

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

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
--------------------

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/showthread.php?t=152093

Microsoft Office Help

 

[Dave Peterson]

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]

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]

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.

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

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]

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

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.

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

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]

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

Make sure you were in the VBE.

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

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.

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" <[email protected]> schreef in bericht
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