insert text in cell and macro 1

[SharonMee]

Hello everyone:

I am trying to write a macro:

Sub Signature()
Sheet3.Activate
If [T4] > 0 Then
r2 = Rows.Count + 1
[T.r2] = "Signature"
End If
End Sub

But it's not working because I am not sure of the syntax to use. I want to insert the Word Signature in column T of the last row + 2 if T4 is greater than 0. E.g. If the last row is 550. I want to insert Signature in T552.
How do I modify this?

thanks so much!

 

[cLFlaVA]

Try this:

Sub Signature()
    Sheet3.Activate
    If Sheet3.Range("T4").Value > 0 Then
        Sheet3.Range("T" & Cstr(Sheet3.UsedRange.Rows.Count + 2)).Value = "Signature"
    End If
End Sub

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[SkipVought]

Sharon,

Sub Signature()
  With Sheet3
    If .[T4] > 0 Then
      With .UsedRange
        r2 = .Row + .Rows.Count
      End With
      .Cells(r2, "T").Value = "Signature"
    End If
  End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Thanks clFlaVA,

That worked!

If I want to select that cell T... and format it with a row height of say 45 and format it with All borders, I did this, but I am getting a debug in the Line Style part of the code:

Sub Signature()
Sheet3.Activate
If Sheet3.Range("T4").Value > 0 Then
Sheet3.Range("T" & CStr(Sheet3.UsedRange.Rows.Count + 3)).Value = "Signature"
With ActiveSheet.Range("T" & CStr(Sheet3.UsedRange.Rows.Count + 3))
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
For i = xlEdgeLeft To xlInsideHorizontal Step 1
With .Borders(i)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next i
End With
Rows("T" & CStr(Sheet3.UsedRange.Rows.Count + 3)).RowHeight = 45

End If
End Sub

Thanks so much

 

[cLFlaVA]

Hi Sharon--

Two things:

One individual cell can not have xlInsideHorizontal and zlInsideVertical borders. That's why your first error is occurring.

Instead of For i = xlEdgeLeft To xlInsideHorizontal Step 1, do For i = xlEdgeLeft To xlEdgeRight Step 1.

Also, when you're accessing a ROW range, you can't use a COLUMN reference. What do I mean? In your line of code: Rows("T" & CStr(Sheet3.UsedRange.Rows.Count + 3)).RowHeight = 45, you refer to column "T". Replace this line with Rows(CStr(Sheet3.UsedRange.Rows.Count + 3)).RowHeight = 45

Hope this helps.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[SharonMee]

Hi cLFlaVA:

Thanks, I have made the changes, but I think the way I have it set up is not giving me the exact results.

My data has 11 rows. What the code below does now is:
in T13 it inserts the word Signature, that's what I want. But then it formats T15 with the All Border and changes the row height of T17 to 45. I only want T13 to be formatted with the above: i.e. with the word Signature, with ALL Border and with a row height of 45. Can you tell what's wrong with this code:
Sub Signature()
Sheet3.Activate
If Sheet3.Range("T4").Value > 0 Then
Sheet3.Range("T" & CStr(Sheet3.UsedRange.Rows.Count + 3)).Value = "Signature"
With ActiveSheet.Range("T" & CStr(Sheet3.UsedRange.Rows.Count + 3))
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
For i = xlEdgeLeft To xlEdgeRight Step 1
With .Borders(i)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next i
End With
Rows(CStr(Sheet3.UsedRange.Rows.Count + 3)).RowHeight = 45
End If
End Sub

Thanks a lot.

 

[cLFlaVA]

That's because when you enter "Signature" into T13, it now becomes the last cell in the UsedRange. Simply remove the +3 from the last two references and you'll be fine.

Places to remove + 3:
[ol][li]With ActiveSheet.Range("T" & CStr(Sheet3.UsedRange.Rows.Count + 3))[/li]
[li]Rows(CStr(Sheet3.UsedRange.Rows.Count + 3)).RowHeight = 45[/li]
[/ol]

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[SharonMee]

Hi ClFlava
Thanks so much, I would pay more attention next time.