number from a cell value instead of a string
 

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.

number from a cell value instead of a string
 

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

number from a cell value instead of a string
 

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.

number from a cell value instead of a string
 

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.

number from a cell value instead of a string
 

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.

number from a cell value instead of a string
 

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.