VB Question

[earthsuiter]

Good Afternoon,

I'm tring to figure out what the VB code I would need to do the following:

Look at all values in column B
If value = "break" then .RowHeight = 2.5

Thanks for your time.
Tim

 

[dave3009]

Hi Tim

Welcome to the Board

Sub mnm()
Dim lst As Long
Dim my, c As Range
lst = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Set my = ActiveSheet.Range("B2:B" & lst)
    For Each c In my
        If c.Value = "break" Then c.RowHeight = 2.5
    Next c
End Sub

 

[VoG]

Hello and welcome to MrExcel.

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "break" Then Rows(i).RowHeight = 2.5
Next i
End Sub

 

[earthsuiter]

Thanks for your help Dave and Peter. That worked just like I needed it to.
One last question along the same line...

Look at all values in column B
If value = "header" go to sheet 2 copy cells a1, b1, c1, d1, e1
and paste them on sheet 1 cells a1, b1, c1, d1, e1

Thanks again,
Tim

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "break" Then
        Rows(i).RowHeight = 2.5
    ElseIf Range("B" & i).Value = "header" Then
        Sheets("Sheet2").Range("A1:E1").Copy Destination:=Range("A1")
    End If
Next i
End Sub

 

[earthsuiter]

Hi Peter,

Thanks once again.
I do have a follow up question because the word header appears more than once in my import file.

Is there anyway to tell Excel, no matter where you see the word "header" copy and paste from sheet 2 a1, b1, c1, d1, e1

Thanks,
Tim

 

[VoG]

The code will do that but always into row 1 of the active sheet. Should it be pasted into the row where "header" is found?

 

[earthsuiter]

Hi Peter,

That's correct. That's exactly what I'm need it to do.

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "break" Then
        Rows(i).RowHeight = 2.5
    ElseIf Range("B" & i).Value = "header" Then
        Sheets("Sheet2").Range("A1:E1").Copy Destination:=Range("A" & i)
    End If
Next i
End Sub

 

[earthsuiter]

What do I need to put if I need to do this for several different words as far as the If, and ElseIf commands?

Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("A" & i).Value = "break" Then
Rows(i).RowHeight = 2.5
If Range("A" & i).Value = "space" Then
Rows(i).RowHeight = 15#
ElseIf Range("A" & i).Value = "header" Then
Sheets("Coding").Range("A1:E1").Copy Destination:=Range("B" & i)
End If
Next i