I need a line of code add to VB please help

M

muzzy

Active Member
Joined
Apr 8, 2014
Messages
333
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

Code: 
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>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
I took what you had copied and added an if statement. See if this does what you need.

Code: 
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
 
Upvote 0
No it did not work

This is what I have
[TABLE="class: grid, width: 291"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Assigned Role
[/TD]
[TD]Assigned[/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-11[/TD]
[TD]ARTHUR[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]CHARLOTTE [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]GISELLE [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LAVONE [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-2-13[/TD]
[TD]MARSHALL [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]MATTHEW [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]PASCAL [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]RACQUEL [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-12[/TD]
[TD]ABRIL [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]BRIA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]DIANNA
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]JERMELL [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]KIMBERLY [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]KRYSTENA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]NATACHA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]NIA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]SHAMEKA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]TAIQUIA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
</tbody>[/TABLE]

This is what I need or looking for?

[TABLE="class: grid, width: 291"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Assigned Role
[/TD]
[TD]Assigned[/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-11[/TD]
[TD]ARTHUR
[/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-11[/TD]
[TD]CHARLOTTE [/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-11[/TD]
[TD]GISELLE [/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-11[/TD]
[TD]LAVONE [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-2-13[/TD]
[TD]MARSHALL [/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-2-13[/TD]
[TD]MATTHEW [/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-2-13[/TD]
[TD]PASCAL [/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-2-13[/TD]
[TD]RACQUEL [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-12[/TD]
[TD]ABRIL [/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-12[/TD]
[TD]BRIA [/TD]
[/TR]
[TR]
[TD]1-2-8-26-1-1-12[/TD]
[TD]DIANNA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]JERMELL [/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]KIMBERLY [/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]KRYSTENA [/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]NATACHA [/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]NIA [/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]SHAMEKA [/TD]
[/TR]
[TR]
[TD]1-2-8-26-2-2-12[/TD]
[TD]TAIQUIA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Subtotal[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Code: 
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
 
Upvote 0
Give this macro a try...
Code: 
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
 
Upvote 0
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
 
Upvote 0
par60056 said:
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
Click to expand...
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.
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...
Sorry when I started my reply there were just 2 of us in the thread. 2 other posts joined while I was typing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Find next visible cell, add a formula and then fill column down
Replies
8
Views
363
MARK858
MARK858
D
  • Question Question
Help with VB Code
Replies
2
Views
265
rlv01
R
P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
489
Pi_Lover
P
M
  • Solved
VBA Use formula within all cells within a range (across numerous columns and rows), referencing row 5 in given column
Replies
4
Views
356
Moley84
M
K
  • Question Question
Help with VB Code to copy workbook
Replies
0
Views
435
kizzie37
K

Forum statistics

Threads
1,224,212
Messages
6,177,163
Members
452,763
Latest member
WH12TTY

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top