Hi all.
I`m new here & a VBA novice "winging it" as I go with a copy of Joh
Walkenbachs Excell 2002 Power Programming with VBA tightly tucked unde
my arm.

This is a simple issue <I hope But i haventgot a clue howw to do it.

I have a module that transfers dates from one workbook into another
The receiving Workbook uses the dates as a start date & end date. Thes
dates then kick off some simple conditional formatting that builds
colur block relating to a time span.

OK...so doing it manually it all works ...as it should & the span ba
is created. Easy.

The VBA code works & the dates are plotted to the correct cells & th
cell formatting is ok.

The issue is that as VBA transfers the data...there is no recogniuse
carriage return for the conditional formatting to know to start. If
click into the cell at the end of the date & press return..th
conditional formatting works.

Therfore my question is if I use a cell.select statement how do I tel
it to do a carriage return?

Has anyone else experienced a similar problem? How did you get aroun
it?
Any advice anyone can offer is greatly appreciated....& will save m
from going bald from ripping my hair out. Thanks;

--
Message posted from http://www.ExcelForum.com

Hi
could you post your current code.
sounds like your date values are transfered qas text and not as date
values

--
Regards
Frank Kabel
Frankfurt, Germany


Hi all.
I`m new here & a VBA novice "winging it" as I go with a copy of John
Walkenbachs Excell 2002 Power Programming with VBA tightly tucked
under my arm.

This is a simple issue <I hope But i haventgot a clue howw to do it.

I have a module that transfers dates from one workbook into another.
The receiving Workbook uses the dates as a start date & end date.
These dates then kick off some simple conditional formatting that
builds a colur block relating to a time span.

OK...so doing it manually it all works ...as it should & the span bar
is created. Easy.

The VBA code works & the dates are plotted to the correct cells & the
cell formatting is ok.

The issue is that as VBA transfers the data...there is no recogniused
carriage return for the conditional formatting to know to start. If I
click into the cell at the end of the date & press return..the
conditional formatting works.

Therfore my question is if I use a cell.select statement how do I
tell
it to do a carriage return?

Has anyone else experienced a similar problem? How did you get around
it?
Any advice anyone can offer is greatly appreciated....& will save me
from going bald from ripping my hair out. Thanks;)


---
Message posted from http://www.ExcelForum.com/

Hi Frank....

This does a litlle bit more than just transfer the dates...but I pasted
it all in here so you can see....pretty much whats going on.
============================================
Public Sub Status1_Copy()
Dim Nextrow As Integer, RfsRow As Integer, X As Integer
Dim RfsInfo(40) As String
Dim RfsNum As Integer


Sheets("StatusH1 2004").Select
Nextrow = 1
Do
If Cells(Nextrow, 1) < "" Then
Nextrow = Nextrow + 1

'MsgBox Cells(Nextrow, 1)
End If
Loop Until Cells(Nextrow, 1) = ""
'Cells(Nextrow, 1).Select
Worksheets("StatusH1 2004").Range("Status1").Copy
Sheets("StatusH1 2004").Select
Cells(Nextrow, 1).Select
ActiveCell.PasteSpecial
Application.ScreenUpdating = False

Sheets("StatusH2 2004").Select
Nextrow = 1
Do
If Cells(Nextrow, 1) < "" Then
Nextrow = Nextrow + 1

'MsgBox Cells(Nextrow, 1)
End If
Loop Until Cells(Nextrow, 1) = ""
'Cells(Nextrow, 1).Select
Worksheets("StatusH2 2004").Range("Status").Copy
Sheets("StatusH2 2004").Select
Cells(Nextrow, 1).Select
ActiveCell.PasteSpecial
Application.ScreenUpdating = False

Sheets("RFS").Select
RfsRow = 2
Do
If Cells(RfsRow, 1) < "" Then
RfsRow = RfsRow + 1
End If
Loop Until Cells(RfsRow, 1) = ""
Cells(RfsRow, 2) = Now

RfsNum = Right(Cells(RfsRow - 1, 1), 3)
Cells(RfsRow, 1) = "UK-04-" & (RfsNum + 1)
For X = 0 To 40
RfsInfo(X) = Cells(RfsRow, X + 1)
Next X
Sheets("StatusH1 2004").Select
Application.ScreenUpdating = True

Nextrow = Nextrow - 1

Cells(Nextrow, 1).Select
'RFS ref
ActiveCell.Offset(2, 3) = RfsInfo(0)
'Cost
ActiveCell.Offset(3, 3) = RfsInfo(39)
'Start Date
ActiveCell.Offset(4, 3) = RfsInfo(37)
'End Date
ActiveCell.Offset(5, 3) = RfsInfo(38)
'Nortime Code
ActiveCell.Offset(8, 3) = RfsInfo(19)
'Customer
ActiveCell.Offset(1, 6) = RfsInfo(9)
'Project
ActiveCell.Offset(2, 6) = RfsInfo(16)
'start force
Cells(Nextrow + 4, 4).Select
ActiveCell = RfsInfo(37)
'end force
Cells(Nextrow + 5, 4).Select
ActiveCell = RfsInfo(38)


Application.CutCopyMode = False

End Sub
============================================

...thx for looking at it;)

M.A.J.O.R DOH! lmao...I can see it......LOL..."RSinfo as STRING"

Thx mate...told u it was simple...sometimes I cant see the wood for the
trees


---
Message posted from http://www.ExcelForum.com/

try:
Application.Calculate
at the end of your module.

woz999 < wrote:

Hi all.
I`m new here & a VBA novice "winging it" as I go with a copy of John
Walkenbachs Excell 2002 Power Programming with VBA tightly tucked under
my arm.

This is a simple issue <I hope But i haventgot a clue howw to do it.

I have a module that transfers dates from one workbook into another.
The receiving Workbook uses the dates as a start date & end date. These
dates then kick off some simple conditional formatting that builds a
colur block relating to a time span.

OK...so doing it manually it all works ...as it should & the span bar
is created. Easy.

The VBA code works & the dates are plotted to the correct cells & the
cell formatting is ok.

The issue is that as VBA transfers the data...there is no recogniused
carriage return for the conditional formatting to know to start. If I
click into the cell at the end of the date & press return..the
conditional formatting works.

Therfore my question is if I use a cell.select statement how do I tell
it to do a carriage return?

Has anyone else experienced a similar problem? How did you get around
it?
Any advice anyone can offer is greatly appreciated....& will save me
from going bald from ripping my hair out. Thanks;)


---
Message posted from http://www.ExcelForum.com/

Add CHR(13) at the end of the text string you're placing
in the cell:

Range("a5")= "Test string" & CHR(13)

-----Original Message-----
Hi all.
I`m new here & a VBA novice "winging it" as I go with a
copy of John
Walkenbachs Excell 2002 Power Programming with VBA
tightly tucked under
my arm.

This is a simple issue <I hope But i haventgot a clue
howw to do it.

I have a module that transfers dates from one workbook
into another.
The receiving Workbook uses the dates as a start date &
end date. These
dates then kick off some simple conditional formatting
that builds a
colur block relating to a time span.

OK...so doing it manually it all works ...as it should &
the span bar
is created. Easy.

The VBA code works & the dates are plotted to the correct
cells & the
cell formatting is ok.

The issue is that as VBA transfers the data...there is no
recogniused
carriage return for the conditional formatting to know to
start. If I
click into the cell at the end of the date & press
return..the
conditional formatting works.

Therfore my question is if I use a cell.select statement
how do I tell
it to do a carriage return?

Has anyone else experienced a similar problem? How did
you get around
it?
Any advice anyone can offer is greatly appreciated....&
will save me
from going bald from ripping my hair out. Thanks;)


---
Message posted from http://www.ExcelForum.com/

.