I need a line of code add to VB please help

[muzzy]

I am trying to copy number down column A:A but when I see the word subtotal in column b:b do not copy in the A:A cell next to it but still copy down. This is the code I am trying to use. There was 3 codes on this page they will all work I just need that line of code add here is the link

http://www.contextures.com/xlDataEntry02.html#FillProg

Sub FillColBlanks_Offset() 'by Rick Rothstein  2009-10-24 'fill blank cells in column with value above 'http://www.contextures.com/xlDataEntry02.html    Dim Area As Range, LastRow As Long   On Error Resume Next   LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _                SearchDirection:=xlPrevious, _                LookIn:=xlFormulas).Row   For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _                SpecialCells(xlCellTypeBlanks).Areas     Area.Value = Area(1).Offset(-1).Value   Next End Sub</pre>

 

[par60056]

I took what you had copied and added an if statement. See if this does what you need.

Sub FillColBlanks_Offset()


'by Rick Rothstein  2009-10-24
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html
Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, _
        LookIn:=xlFormulas).Row
For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow).SpecialCells(xlCellTypeBlanks).Areas
    If (Area(1).Offset(0, 1).Value <> "subtotal") Then
        Area.Value = Area(1).Offset(-1).Value
    End If
Next
End Sub

 

[muzzy]

No it did not work

This is what I have

Assigned Role	Assigned
1-2-8-26-1-1-11	ARTHUR
	CHARLOTTE
	GISELLE
	LAVONE
	Subtotal
1-2-8-26-1-2-13	MARSHALL
	MATTHEW
	PASCAL
	RACQUEL
	Subtotal
1-2-8-26-1-1-12	ABRIL
	BRIA
	DIANNA
	Subtotal
1-2-8-26-2-2-12	JERMELL
	KIMBERLY
	KRYSTENA
	NATACHA
	NIA
	SHAMEKA
	TAIQUIA
	Subtotal

<colgroup><col><col></colgroup><tbody>
</tbody>

This is what I need or looking for?

Assigned Role	Assigned
1-2-8-26-1-1-11	ARTHUR
1-2-8-26-1-1-11	CHARLOTTE
1-2-8-26-1-1-11	GISELLE
1-2-8-26-1-1-11	LAVONE
	Subtotal
1-2-8-26-1-2-13	MARSHALL
1-2-8-26-1-2-13	MATTHEW
1-2-8-26-1-2-13	PASCAL
1-2-8-26-1-2-13	RACQUEL
	Subtotal
1-2-8-26-1-1-12	ABRIL
1-2-8-26-1-1-12	BRIA
1-2-8-26-1-1-12	DIANNA
	Subtotal
1-2-8-26-2-2-12	JERMELL
1-2-8-26-2-2-12	KIMBERLY
1-2-8-26-2-2-12	KRYSTENA
1-2-8-26-2-2-12	NATACHA
1-2-8-26-2-2-12	NIA
1-2-8-26-2-2-12	SHAMEKA
1-2-8-26-2-2-12	TAIQUIA
	Subtotal

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[NeonRedSharpie]

Sub lineOfCode()
    For x = 3 To Cells(Rows.Count, "B").End(xlUp).Row
        If UCase(Cells(x, 2)) <> "SUBTOTAL" And Cells(x, 1) = "" Then
            Cells(x, 1).Value = Cells(x - 1, 1).Value
        End If
    Next
End Sub

 

[Rick Rothstein]

Give this macro a try...

Sub FillInAssignedRoles()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  On Error Resume Next
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar.Resize(Ar.Rows.Count - 1).FormulaR1C1 = "=R[-1]C"
    Ar.Value = Ar.Value
  Next
  On Error GoTo 0
End Sub

 

[par60056]

try changing subtotal to Subtotal in the IF statement. The code has a lower case S and the actual value has an upper case S

 

[Rick Rothstein]

try changing subtotal to Subtotal in the IF statement. The code has a lower case S and the actual value has an upper case S

You should either "Reply with quote" (reducing the text to the relevant sections) or mention whose post you are directing your comment to. Given that you didn't, I would like to point out that the code I posted does not need a fix for the word "subtotal" as my code does not directly check for that word.

 

[par60056]

You should either "Reply with quote" (reducing the text to the relevant sections) or mention whose post you are directing your comment to. Given that you didn't, I would like to point out that the code I posted does not need a fix for the word "subtotal" as my code does not directly check for that word.

Sorry when I started my reply there were just 2 of us in the thread. 2 other posts joined while I was typing.