Maro Required - Fill in texts in cells based on other cell values

[Blaster1]

Hi Guys,

I am new here and want a MACRO help.
I want to use a MACRO code to be applied to ALL Worksheets within one workbook.

If cells A2 (as an example) containts text (any text) then fill in B2 with "John Smith" C2 with date "12 09 21" and D2 with "John Smith" but with special font (MISTRAL Font).
I want this to be applied for all rows associated with column range A2:A11 (when there is not text in a any cell of range A2:A11 like A6 in below image, then do nothing) then applies it to all worksheets in the workbook.
Thanks.

 

[maabadi]

Welcome to MrExcel Message Board.
Try this:

Sub Test()
  Dim Lr As Long, Sh As Worksheet, i As Long, Ar() As Variant
  For Each Sh In Worksheets
  With Sh
  Lr = .Cells(Rows.Count, "A").End(xlUp).Row
  .Range("C2:C" & Lr).NumberFormat = "mm dd yyyy"
  .Range("D2:D" & Lr).Font.Name = "Mistral"
  For i = 2 To Lr
  If .Range("A" & i).Value <> "" Then
  .Range("B" & i).Value = "John Smith"
  .Range("C" & i).Value = CDate(44539)
  .Range("D" & i).Value = "John Smith"
  End If
  Columns("A:D").AutoFit
  Next i
  End With
  Next Sh
End Sub

 

[Blaster1]

Awesome!! it worked, thank you very much.
Probably you can help me expand the code a bit ...

See below Code followed by the picture ...

Sub Test()
Dim Lr As Long, Sh As Worksheet, i As Long, Ar() As Variant
For Each Sh In Worksheets
With Sh
Lr = .Cells(Rows.Count, "D").End(xlUp).Row
.Range("F2:F" & Lr).NumberFormat = "mm dd yyyy"
.Range("G2:G" & Lr).Font.Name = "Mistral"
For i = 5 To Lr
If .Range("D" & i).Value <> "" Then
.Range("E" & i).Value = "John Smith"
.Range("F" & i).Value = CDate(44539)
.Range("G" & i).Value = "John Smith"
End If
Columns("D:G").AutoFit
Next i
End With
Next Sh
End Sub

I want to expand the Macro to also work as:
REPLACE ALL blank Cells within A5:H14 with "text" ..... (knowing that other sheets may have different row counts, not only 10 rows as Sheet1 (4); probably more, probably less but ALWAY start from A5 and going down).
Thanks.

 

[maabadi]

if your data at all sheets start from A5 Try this:

Sub Test()
  Dim Lr As Long, Sh As Worksheet, i As Long, Ar() As Variant
  For Each Sh In Worksheets
  With Sh
  Lr = .Cells(Rows.Count, "A").End(xlUp).Row
  .Range("F2:F" & Lr).NumberFormat = "mm dd yyyy"
  .Range("G2:G" & Lr).Font.Name = "Mistral"
  For i = 2 To Lr
  If .Range("D" & i).Value <> "" Then
  .Range("E" & i).Value = "John Smith"
  .Range("F" & i).Value = CDate(44539)
  .Range("G" & i).Value = "John Smith"
  End If
  Columns("A:H").AutoFit
  Next i
  .Range("A5:H" & Lr).SpecialCells(xlCellTypeBlanks).Value = "Text"
  End With
  Next Sh
End Sub

 

[Blaster1]

Thanks Mate, all worked well. Appreciated, thanks for the help.
Cheers,

 

[Peter_SSs]

Here is another way that I think does what you want, but with a lot less looping.

Sub FillValues()
  Dim ws As Worksheet
  Dim a(1 To 3) As Variant
  
  a(1) = "John Smith":  a(2) = DateSerial(2021, 12, 9): a(3) = a(1)
  For Each ws In Worksheets
    With ws.Range("E5:G" & ws.Range("A" & Rows.Count).End(xlUp).Row)
      .Value = a
      On Error Resume Next
      Intersect(.Columns(0).SpecialCells(xlBlanks).EntireRow, .EntireColumn).ClearContents
      On Error GoTo 0
      .Columns(2).NumberFormat = "dd mm yy"
      .Columns(3).Font.Name = "Mistral"
      With .Offset(, -4).Resize(, 8)
        On Error Resume Next
        .SpecialCells(xlBlanks).Value = "text"
        On Error GoTo 0
        .EntireColumn.AutoFit
      End With
    End With
  Next ws
End Sub

 

[Blaster1]

Thanks, eally helpful ...
I am trying to automate some reporting rather than manually creating reports ..... i'm alsmot there ... only two issues im facing now ...
1- if a cell contains "Text1" or "Text2" or "Text3" the select this row and delete ALL rows underneath ...
2- the Biggest problem is that few sheets contains rows that I need to delete ... those are random per each page. Cannot find a patter to link to so I can generate a code ...
cheers,

 

[Peter_SSs]

1- if a cell contains "Text1" or "Text2" or "Text3" the select this row and delete ALL rows underneath ...

Will those particular text values be in a particular column or could they be in various columns?

2- the Biggest problem is that few sheets contains rows that I need to delete ... those are random per each page. Cannot find a patter to link

Not sure I understand that. Are you simply saying that some sheets require rows to be deleted and some do not?

 

[Blaster1]

1. yes in specific column.
2. yes, some sheets requires rows to be deleted and some not. The ones with rows to be deleted, they are at random row position, there no specific (I will find later a way to determin how those to be deleted; not needed now).

 

[Peter_SSs]

1. yes in specific column

Which column?