automatically fill in a cell if the adjacent cell has any value

[Guest]

This should be a simple question for you programmers out there.
I have more than 100 spreadsheats all with the same structure so if I can
write a macro it will save me lots of repetitive action.
I need to fill in a column with the value which is always contained in cell
A1. If there are values in column B then fill in adjacent cell in column A
with the value named in cell A1.
In other words, if cell A1 ="Detroit" and there are some/any values in
column B, then fill in the cells in column A as "Detroit". Some spreadsheats
have 5 rows to be filled in and some have 100 rows to be filled in.

I have tried several different times using a book I bought at Barnes &
Nobles, but I am having a hard time implementing a solution.

Thanks

 

[Guest]

Hi!

I hope, have understood you right, so try this:

Create a modul and copy the code below in this modul, then start the macro:

Sub FillIn()
For SheetCount = 1 To Worksheets.Count
Worksheets(SheetCount).Activate
Cells(1).Select
Selection.CurrentRegion.Select
LastRow = Selection.Rows.Count
For RowCount = 1 To LastRow
If Cells(RowCount, 2).Value <> "" Then
Cells(RowCount, 1).Value = Cells(1, 1).Value
End If
Next RowCount
Next SheetCount
End Sub

 

[davidm]

Try:



Sub CopyA1()
Set rng = Range("b1:b" & [b65536].End(xlUp).Row)
For Each c In rng
If Not IsEmpty(c) Then
c.Offset(0, -1).Value = Range("A1").Value
End If
Next
End Sub


To run through all your (100??) spreadsheets use:
Sub CopyA1Plus()
Application.ScreenUpdating=False
Set AcSheet = ActiveSheet
For Each sh In Worksheets
sh.Activate
Set rng = Range("b1:b" & [b65536].End(xlUp).Row)
For Each c In rng
If Not IsEmpty(c) Then
c.Offset(0, -1).Value = Range("A1").Value
End If
Next
Next
AcSheet.Select
Application.ScreenUpdating=True
End Su

 

[Tom Ogilvy]

Dim sh as Worksheet, rng as Range, cell as Range
for each sh in ActiveWorkbook.Worksheets
set rng = sh.Range(sh.Cells(1,2),sh.Cells(rows.count,2).End(xlup))
for each cell in rng
if not isempty(cell) then
cell.offset(0,-1).Value = sh.Range("A1").Value
end if
next
Next

 

[Don Guillett]

One way would be to use a for/each loop
for each c in range("b2:b200")
if len(c)>1 then c.offset(0,-1).value=cell(1,1)
next