VBA...How do you state a carriage return?

W

woz999

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

Frank Kabel

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

woz999

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:rolleyes:
 
Top