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

V

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
 
V

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



Simon Lloyd said:
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 said:
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
Click to expand...


--
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
Click to expand...
 
D

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

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
 
D

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

Dave Peterson said:
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.
Click to expand...
Click to expand...
 
V

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

Dave Peterson said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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

Dave Peterson said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch 0
Trouble selecting multiple sheets using RDB code 11
Object Error: what am i doing wrong?? 11
Can't get soap client to connect in Excel 2010 64 bit 0
Whatever I do I get the wrong result 5
How do I test whether a string is a valid cell reference? 5
Replacing the formula text's substring 2
how do I add multiple email address? 6

Top