Writing VBA for Inserting Text in a multiple cells.

[sirmacademy]

I want to insert in a cell a text "Saturday" and "Sunday" but I find it difficult to find the pattern inserting it.

Thank you for your help.

 

[JackDanIce]

What cell does it need to be in and what or should the cell show it?

 

[sirmacademy]

I want to insert in C3, C9 C10, C16 C17, C23 C24, C30 C31.

 

[Rick Rothstein]

I want to insert in C3, C9 C10, C16 C17, C23 C24, C30 C31.

Your thread title says "inserting text" which implies the cells already have some text in them and you want to insert your text somewhere within it. If that is the case, you need to show us the existing text you have and what it should look like after the insertion.

 

[JackDanIce]

@sirmacademy your screen isn’t visible. What you can see when you ask your question... is not what anyone reading this post can see, how would you explain your problem to a blind person?

As Rick points out, if you use the term insert, then be precise.

What is in the cell? Give example since your screen is NOT visible.

What should it look like afterwards? For avoidance of ambiguity, SHOW expected output.

Do not assume anyone reading can see your screen, how can they?

What does C3, C9, C10, C16, C17, C23, C24, C30, C31 contain? Why can’t you type “Saturday” or “Sunday” into them?

 

[sirmacademy]

Good day, Sir! Thank you very much for your concern. But I find it difficult to attached pictures or images.

 

[sirmacademy]

I have the code below:
Sub AddSaturdaySunday()
' get the last row
Dim startRow As Long, lastRow As Long
startRow = 3
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row

Dim i As Long, Mydate As String
Dim sDate As String


' Go through the marks columns
For i = startRow To lastRow
Mydate = Sheet1.Range("B" & i).Value


' Check Mydate and classify accordingly then print Saturday and Sunday
If Mydate = "2018-08-11" Then
sDate = "Saturday"


ElseIf Mydate = "2018-08-12" Then
sDate = "Sunday"

Else
'For this part it must leave the original data


End If

' Write out the class to column C
Sheet1.Range("C" & i).Value = sDate
Next

End Sub

I want to print it in a Specific Text/ Date as reflected in my conditional statement. Thank you!

 

[JackDanIce]

Only from your code is it clear you're working with column B. Check your first post, did you mention column B anyway or output into column C?

This is why it's important to be precise or you would have had a suggestion much sooner! One suggestion:

Sub M1()

    Dim x       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.Count, 2).End(xlUp).Row
    arr = Cells(3, 2).Resize(x - 2).Value
    For x = LBound(arr, 1) To UBound(arr, 1)
        arr(x, 1) = Format(arr(x, 1), "DDDD")
        If Left$(arr(x, 1), 1) <> "S" Then arr(x, 1) = vbNullString
    Next x
    Cells(3, 3).Resize(UBound(arr, 1)).Value = arr
    
    Erase arr
            
End Sub

(PS Hope @Rick Rothestein can post a shorter suggestion using Evaluate.. still trying to learn/understand!)

 

[Rick Rothstein]

(PS Hope @Rick Rothestein can post a shorter suggestion using Evaluate.. still trying to learn/understand!)

Here you go...

If there are no blank cells within the dates listed in Column B...

[table="width: 500"]
[tr]
	[td]Sub M2()
  With Range("B3", Cells(Rows.Count, "B").End(xlUp))
    .Offset(, 1) = Evaluate(Replace("IF(WEEKDAY(@,2)>5,TEXT(@,""dddd""),"""")", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

and if there could be blank cells within the dates listed in Column B...

[table="width: 500"]
[tr]
	[td]Sub M3()
  With Range("B3", Cells(Rows.Count, "B").End(xlUp))
    .Offset(, 1) = Evaluate(Replace("IF((@<>"""")*(WEEKDAY(@,2)>5),TEXT(@,""dddd""),"""")", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[JackDanIce]

Thank you Rick, hopefully can decipher and use more often.

I know you need to create arrays of equal size and then I get stuck with rest of it. Usually end up with #VALUE in all the cells, suggesting output isn't correct Will practise with these tonight when home, thx again