Writing VBA for Inserting Text in a multiple cells.

sirmacademy

New Member
Joined
Aug 12, 2018
Messages
17
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
@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

New Member
Joined
Aug 12, 2018
Messages
17
Good day, Sir! Thank you very much for your concern. But I find it difficult to attached pictures or images.
 

sirmacademy

New Member
Joined
Aug 12, 2018
Messages
17
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
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:
Code:
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!)
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,505
Office Version
2010
Platform
Windows
(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...
Code:
[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...
Code:
[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

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
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
 

Forum statistics

Threads
1,082,151
Messages
5,363,437
Members
400,737
Latest member
vipamuk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top