This code piece works it creates a header row except it stores the value of
the cell #17 into cell #3 as a number value instead of a string value and I
don't know how to change it without rewriting the rest of it.
With ActiveWorkbook.Worksheets("Sheet1")
XXXXX
XXXXXother stuff
..Rows(2).Insert
..Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
Value = .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With
THANKS.

..Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
.Value = "'" & .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With

--
Regards,
Tom Ogilvy


"Janis" wrote:

This code piece works it creates a header row except it stores the value of
the cell #17 into cell #3 as a number value instead of a string value and I
don't know how to change it without rewriting the rest of it.
With ActiveWorkbook.Worksheets("Sheet1")
XXXXX
XXXXXother stuff
.Rows(2).Insert
.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
Value = .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With
THANKS.

The value is turning out to be a number 4 instead of the text in (3,17). I
am going to check the field type.
thanks,

"Tom Ogilvy" wrote:

.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
.Value = "'" & .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With

--
Regards,
Tom Ogilvy


"Janis" wrote:

This code piece works it creates a header row except it stores the value of
the cell #17 into cell #3 as a number value instead of a string value and I
don't know how to change it without rewriting the rest of it.
With ActiveWorkbook.Worksheets("Sheet1")
XXXXX
XXXXXother stuff
.Rows(2).Insert
.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
Value = .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With
THANKS.

Cells(3,17) is Q3 is that the cell you want

? cells(3,17).Address
$Q$3

--
Regards,
Tom Ogilvy


"Janis" wrote:

The value is turning out to be a number 4 instead of the text in (3,17). I
am going to check the field type.
thanks,

"Tom Ogilvy" wrote:

.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
.Value = "'" & .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With

--
Regards,
Tom Ogilvy


"Janis" wrote:

This code piece works it creates a header row except it stores the value of
the cell #17 into cell #3 as a number value instead of a string value and I
don't know how to change it without rewriting the rest of it.
With ActiveWorkbook.Worksheets("Sheet1")
XXXXX
XXXXXother stuff
.Rows(2).Insert
.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
Value = .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With
THANKS.

One additional problem, beyond the one pointed out by Tom, is your nesting of With structures. Your
..Cells(3,17) will be relative to .Cells(2,3), not relative to the sheet. Your reference
(simplified) is actually

ActiveWorkbook.Worksheets("Sheet1").Cells(2, 3).Cells(3, 17)

Which refers to cell S4, not Q3....

You could fix this by using

Dim mySht As Worksheet
Set mySht = ActiveWorkbook.Worksheets("Sheet1")

and then change .Cells(3, 17) to mySht.Cells(3, 17)


HTH,
Bernie
MS Excel MVP


"Janis" wrote in message
...
This code piece works it creates a header row except it stores the value of
the cell #17 into cell #3 as a number value instead of a string value and I
don't know how to change it without rewriting the rest of it.
With ActiveWorkbook.Worksheets("Sheet1")
XXXXX
XXXXXother stuff
.Rows(2).Insert
.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
Value = .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With
THANKS.

good catch, I missed that

Another fix might be

.Value = "'" & .parent.Cells(3, 17)


--
Regards,
Tom Ogilvy

"Bernie Deitrick" wrote:

One additional problem, beyond the one pointed out by Tom, is your nesting of With structures. Your
..Cells(3,17) will be relative to .Cells(2,3), not relative to the sheet. Your reference
(simplified) is actually

ActiveWorkbook.Worksheets("Sheet1").Cells(2, 3).Cells(3, 17)

Which refers to cell S4, not Q3....

You could fix this by using

Dim mySht As Worksheet
Set mySht = ActiveWorkbook.Worksheets("Sheet1")

and then change .Cells(3, 17) to mySht.Cells(3, 17)


HTH,
Bernie
MS Excel MVP


"Janis" wrote in message
...
This code piece works it creates a header row except it stores the value of
the cell #17 into cell #3 as a number value instead of a string value and I
don't know how to change it without rewriting the rest of it.
With ActiveWorkbook.Worksheets("Sheet1")
XXXXX
XXXXXother stuff
.Rows(2).Insert
.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
With .Cells(2, 3)
Value = .Cells(3, 17) //this neeeds to be text value
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18
.Font.Color = vbWhite
End With
End With
THANKS.