Using a function to update negative cells I am working on a spreadsheet that has

ID: 3560947 • Letter: U

Question

Using a function to update negative cells

I am working on a spreadsheet that has missing cell information. What I am trying to do is if the prior cell's Company Name matches -- to fill the empty slot with the same info with the line prior. If the information does NOT match, it should remain blank. The information is in 2 different cells.

Is there anyway to do this?

Here is a sample:

Code Code 1 Code 2 Code 3 Name $ this year Code 4 Comments 0010 10&R4 10 &R4 Disney 8,238.00 2- 0056 10071 10 071 Donald Duck 10,688.00 2- 0010 100C8 10 0C8 Daffy Duck 10,674.00 XP 0056 10107 10 107 Mickey Mouse 28,746.00 0% 0056 10107 8Y 107 Mickey Mouse 11,015.00 Example 1 -- since name is same, code in G should be the same 0056 10109 8Y 2IJ Casper 0.00 1- 0056 10109 8Y 109 Casper 7,982.00 Same here w/casper 0056 10109 8Y 111 Casper 0.00 Same here w/casper 0010 10112 10 115 Scooby Doo 3,670.00 0% 0010 10112 10 113 Scooby Doo 3,180.00 0% 0010 10112 10 112 Scooby Doo 11,580.00 0% 0010 10112 10 2QX Scooby Doo 4,511.00 0% 0010 10112 8Y 112 Scooby Doo 6,691.00 same here with Scooby Doo 0056 10119 10 119 Minnie Mouse 7,151.00 2% 0010 1012F 10 12F Fred Flintstone 7,679.00 1% 0056 1012G 10 12G Barney Rubble 27,912.00 9% 0056 1012G 10 12J Barney Rubble 1,422.00 Same here with Barney Rubble

Explanation / Answer

*I've assumed that the data you posted is typical of your real data and all we're doing is filling the blank(s) from the cell above.%^%&

ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in. Ensure that the sheet with the data on is the visible sheet and run the code.2w45:

Sub FillBlanks1()
Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Columns("A:G").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For Each Area In Range("G2:G" & LastRow). _
                   SpecialCells(xlCellTypeBlanks).Areas
With Area
    .NumberFormat = Area(1).Offset(-1).NumberFormat
    .Value = Area(1).Offset(-1).Value
End With
Next
End Sub

Navigate

Using a function generator as shown in Figure 3(b) (square wave output – 0 to 5

Using a function, add the numbers of the Hours Billed column together, but only

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Using a function to update negative cells I am working on a spreadsheet that has

Question

Explanation / Answer

Related Questions

Navigate