User Posts: Sabrina Ayon
Microsoft Excel offers a variety of methods for counting multiple conditions. We may do this by filtering data using PivotTable or the COUNTIF function. ...
Sabrina Ayon
The features of Microsoft Excel make entering dates simpler. For instance, 3/13 becomes 13 Mar. This is quite annoying when we type that which we wouldn't want ...
While working with Microsoft Excel, create a complicated formula that depends on a number of different inputs, and we wish to understand how altering these ...
Microsoft Excel may determine the separation between two specific cities or locations on the planet. It is essential to be able to determine the distance ...
While working with Microsoft Excel, we may have some data which we need to group and summarize. By minimization and maximization, the gathered data can be ...
Flipping the data simply means swapping the column values. Therefore indicates the very last item in the column should be the first value in the opposite ...
While working with Microsoft Excel, we may have to work with lots of data. We may build many spreadsheets when dealing with a lot of information in order to ...
While working with Microsoft Excel, there is at least one sheet across every spreadsheet. We may build many spreadsheets when dealing with a lot of information ...
While working with Microsoft Excel, we insert graphs to make it possible to visualize data collections. We zoom an Excel graph to see it up close. The chart's ...
While working with Microsoft Excel, to emphasize specific numbers or make specific cells simple to recognize we use Conditional Formatting. According to a ...
The fact that Microsoft Excel can only handle balancing designs in which each sample does have an equal amount of observations is among its most notable ...
While working with Microsoft Excel, we may utilize examining large quantities of data. There are many various methods to modify the worksheets in Microsoft ...
While working with Microsoft Excel, it's vital to sometimes label cells with certain values. Users frequently need to indicate negative and positive numbers ...
![[Fixed!] Sub or Function Not Defined in Excel VBA](https://www.exceldemy.com/wp-content/uploads/thumbs_dir/sub-or-function-not-defined-vba-excel-1-1znr4y8oqh3jlll92c1o3slv8f0pmkecwp9buayu3mbw.png)
‘Sub or Function not defined’ is a compilation mistake in Excel VBA. When anything specified by name cannot be found, VBA shows this warning. Before being run, ...
While working with Microsoft Excel, sometimes we have to change the orientation of some data. We may need to arrange the texts in the most accessible way ...
- 1
- 2
- 3
- …
- 10
- Next Page »
” title=”
Hello, E!
Did you follow those steps properly? If any of those did not work, then try this out!
Sub Inert_rows()
Dim rng As Long
For rng = range(“C” & Rows.Count).End(xlUp).Row To 1 Step -1
With Cells(rng, 3)
If IsNumeric(.Value) And Not IsEmpty(.Value) Then
Rows(rng + 1).Resize(.Value).Insert
range(Replace(“G#:BW#”, “#”, rng)).Copy Destination:=range(“G” & rng + 1).Resize(.Value)
End If
End With
Next rng
End Sub
Hello, VICKIE WATT!
Thanks for your comment. Yes, this is date static.
You are most welcome, Wayne Edmondson!
Stay Tuned!
Regards,
Sabrina Ayon
Author, ExcelDemy.
Hello, WAYNE EDMONDSON!
Thanks for sharing your thoughts with us!
Stay Tuned!
Regards,
Sabrina Ayon
Author, ExcelDemy.
Hello, HANNES!
You can use the same code to generate 2 screenshots (from 2 different ranges) from the same worksheet. All you have to do is, while selecting any range press Ctrl. Then, just Run the code.
Or, you can use the code below, this will convert your excel file range to word document.
Private Sub EmailSS(rng As Range, rng2 As Range, strName As String)
‘To Open Email
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject(“Outlook.Application”)
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)
With outMail
.To = strName
.Subject = “** Check this **”
.Importance = olImportanceHigh
.Display
End With
‘To Get Word Document
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor
‘To Take Screenshot
rng.Copy
wordDoc.Paragraphs(1).Range.PasteSpecial , , , , wdPasteBitmap
wordDoc.Content.InsertParagraphAfter
rng2.Copy
wordDoc.Paragraphs(2).Range.PasteSpecial , , , , wdPasteBitmap
outMail.HTMLBody = “Timesheets Submitted by ” & strName & “
” & _
Range(“Text”) & vbNewLine & outMail.HTMLBody
End Sub
Hope this will help you!
Thanks for sharing your problem with use.
Hello, Jim!
Thanks for your comment!
Glad that you noticed!
But it’s not a problem or it’s not even any bug, as I set snam to sf and sf is declared.
I did not set any path where I would put the pdf to print it. That’s the reason I did not set any file path location or declare the strPathFile variable. This code will automatically save into your active disk location. If you want to save the file in a specific file path you can initialize strPathFile and put your path manually.
I will suggest that you please download the workbook and run the codes. After that, if you have any queries you can ask!
Good Luck!
Hello, DEMI!
Please follow step 7, you will surely get the Regions button. If you miss any of those steps you won’t get the result. Follow each instruction step by step hopefully, you will find the Regions button.
https://www.exceldemy.com/create-custom-regions-in-excel-3d-maps/#Step_7_Create_Custom_Regions
Good Luck!
Hello, DJ!
If you just Autofit all the selected rows you can use this code.
Sub Autofit_Rows()
Range(“A1:A10”).Select
Selection.Rows.AutoFit
Range(“A1”).Select
End Sub
After Autofit the rows, double the height of all rows is not possible actually. You can Autofit with some padding, please try this code. Hope this will help you.
Sub AutoFitRows()
Dim ws As Worksheet
Dim rng As Range
Application.ScreenUpdating = False
For Each ws In ActiveWindow.SelectedSheets
With ws.UsedRange
.EntireRow.AutoFit
For Each rng In .Rows
rng.RowHeight = rng.RowHeight + 15
Next rng
.VerticalAlignment = xlCenter
End With
Next ws
Application.ScreenUpdating = True
End Sub
Hello, JAMES!
Those codes work properly for pivot table range. Can you please send me your Excel file at [email protected]? So that, I can help you.
Thanks!
Hello, ROWAN!
Check this article. This may help you.
https://www.exceldemy.com/excel-automatically-send-email-when-condition-met/#2_Send_Email_Automatically_Based_on_a_Due_Date_Using_VBA_Code
Use this code to send 20+ emails in one go each with a unique range. Just change the condition and range as per your requirements.
Public Sub Send_Email_Automatically()
Dim rngD, rngS, rngT As Range
Dim ob1, ob2 As Object
Dim LRow, x As Long
Dim l, strbody, rSendValue, mSub As String
On Error Resume Next
Set rngD = Application.InputBox(“Deadline Range:”, “Exceldemy”, , , , , , 8)
If rngD Is Nothing Then Exit Sub
Set rngS = Application.InputBox(“Email Range:”, “Exceldemy”, , , , , , 8)
If rngS Is Nothing Then Exit Sub
Set rngT = Application.InputBox(“Email Topic Range:”, “Exceldemy”, , , , , , 8)
If rngT Is Nothing Then Exit Sub
LRow = rngD.Rows.Count
Set rngD = rngD(1)
Set rngS = rngS(1)
Set rngT = rngT(1)
Set ob1 = CreateObject(“Outlook.Application”)
For x = 1 To LRow
rngDValue = “”
rngDValue = rngD.Offset(x – 1).Value
If rngDValue <> “” Then
If CDate(rngDValue) – Date <= 7 And CDate(rngDValue) - Date > 0 Then
rngSValue = rngS.Offset(x – 1).Value
mSub = rngT.Offset(x – 1).Value & ” on ” & rngDValue
l = “
”
”strbody = “
strbody = strbody & “Hello! ” & rngSValue & l
strbody = strbody & rngT.Offset(x – 1).Value & l
strbody = strbody & “”
Set ob2 = ob1.CreateItem(0)
With ob2
.Subject = mSub
.To = rSendValue
.HTMLBody = strbody
.Send
End With
Set ob2 = Nothing
End If
End If
Next
Set ob1 = Nothing
End Sub
Hello, JAN (YAN) WOELLHAF!
If those code does not work for you, try this one! Hope this will help you.
Sub InsertPic()
Dim path As String, photo As Picture, cell As Range
path = “E:\test” & Range(“C3”).Value & “.png”
Set cell = ActiveCell.MergeArea
Set photo = ActiveSheet.Pictures.Insert(PicPath)
With photo
.ShapeRange.LockAspectRatio = msoFalse
.Left = ImageCell.Left
.Top = ImageCell.Top
.Width = ImageCell.Width
.Height = ImageCell.Height
End With
End Sub
Hello, AMNA SHAHBAZ!
This is Sabrina, one of the authors of Exceldemy. First of all, thank you for your comment. Actually, we don’t work with jama software. So, we are not sure whether it’s possible or not!
Hello, GERT RENKIN!
Try this code to hide rows except matching values. Hope this will help you!
Sub Hide_Rows()
Dim rng As Long
With Sheets(“Sheet1”)
For rng = 1 To 8
If Cells(5, 1).Value <> Cells(rng, 1).Value Then
.Rows(rng).EntireRow.Hidden = True
End If
Next rng
End With
End Sub
Hello, LUIS!
To apply the code for all sheets you have to write the code in a module. For this, go to the Developer tab > Visual Basic. Then, go to Insert > Module. And, paste the code there. This will work for all your active sheets.
Hello, GRIJESH PRAJAPATI!
If the list of keywords has 2 or more matchable values separated with a comma (,), this will highlight automatically by using the following VBA code.
https://www.exceldemy.com/highlight-specific-text-in-excel-cell-vba/#3_VBA_Code_to_Highlight_Multiple_Specific_Texts_in_a_Range_of_Cells_in_Excel_Case-Insensitive_Match
Hello, THOMAS SAULNIER!
Yes! you can add 2 extra blank cells. For this, follow the code below. Hope this will help you!
Sub Insert_Rows()
For rng = Cells(Rows.Count, “C”).End(xlUp).Row To 2 Step -1
For row_num = 2 To Cells(rng, “C”).Value + 3
Cells(rng + 1, “C”).EntireRow.Insert
Next row_num, rng
End Sub
Hello, DIANA!
There is no problem with your code. What’s the problem actually?
Can you please email me the dataset here; [email protected]
Or you can visit the following article, this may help you to fix your problem.
https://www.exceldemy.com/automatically-send-email-from-excel-based-on-date/
Hello, HARVEINDRAN!
Please check the following articles. Hopefully, this will solve your problem!
https://www.exceldemy.com/excel-combine-data-from-multiple-sheets/
https://www.exceldemy.com/combine-multiple-excel-files-into-one-workbook-separate-sheets/
Hello, CRISTIAN!
Please check the article below, you will find the answer to your question.
https://www.exceldemy.com/calculate-travel-time-between-two-cities-in-excel/
Hello, AMIT!
I’m really sorry to say that the STOCKHISTORY function won’t work in Google Sheet as google sheet has limited functions to perform but the TODAY function will work adequately. You need to work on an Excel sheet.
Hello, ADITYA AGARWAL!
Try This code. This will automatically protect your spreadsheet after the sheet has been closed.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WrkSht As Worksheet
Const Password As String = “pass1234”
For Each WrkSht In ThisWorkbook.Worksheets
WrkSht.Protect Password:=Password
Next WrkSht
End Sub
Hope this will help you!
Hello, JOHN!
You can run the code by pressing the keyboard shortcut F5.
Hello, JOSHUA KROGER!
Please Check the first and the third example. I drop the link here.
https://www.exceldemy.com/excel-macro-to-send-email-automatically/#1_Apply_Excel_VBA_Macro_to_Send_Email_Automatically_Based_on_Cell_Value
https://www.exceldemy.com/excel-macro-to-send-email-automatically/#3_Use_Excel_Macro_to_Send_Email_Automatically_with_Attachments
Hope you will get the solution.
Else you can try this! To use this code, first, you need to create a button.
Private Sub CommandButton1_Click()
On Error GoTo ErrHandler
Dim obj As Object
Set obj = CreateObject(“Outlook.Application”)
Dim objE As Object
Set objE = obj.CreateItem(olMailItem)
Dim rng As Range
Set rng = Range(“A4:A8” & Cells(Rows.Count, “A”).End(xlUp).Row)
Dim rng1 As Range
Dim int As Integer
Dim mailID, CCmailID As String
For Each cell In rng
If Trim(mailID) = “” Then
mailID = cell.Offset(1, 0).Value
Else
If Trim(CCmailID) = “” Then
CCmailID = cell.Offset(1, 0).Value
Else
CCmailID = CCmailID & vbCrLf & “;” & cell.Offset(1, 0).Value
End If
End If
Next cell
Set rng = Nothing
With objE
.To = mailID
.CC = CCmailID
.Subject = “Sending Email with VBA.”
.Body = “This is a Sample Mail.”
.Display
End With
Set objE = Nothing: Set obj = Nothing
ErrHandler:
‘
End Sub
Hello, Red!
In the 10th line, there is a correction.
For Each Value In st.Range(“ClassLocations”)
Try this!
And make sure you are writing the code in a Module.
Hello, Anita Sessa!
Do you want to get the same information from a worksheet to another worksheet? If is that so, you can check this Link:
https://www.exceldemy.com/extract-data-from-one-sheet-to-another-in-excel-using-vba/
There are three examples to get the same information from one sheet to another.
Hello, Larry!
To get the values from a variable workbook name you can use this code. This will show the variable workbook name in a Msg Box.
Sub GetValues()
Dim wbName As String
wbName = ActiveWorkbook.Name
MsgBox wbName
End Sub
If you want to get all the active workbooks’ names you can use this.
Sub GetValues()
Dim wbName As Workbook
For Each wbName In Workbooks
ActiveCell = wbName.Name
ActiveCell.Offset(1, 0).Select
Next
End Sub
Hello, CY!
Yeah, actually this is because I set the cell first as “Range(“C5:C” & row)”. Here as I set cell C5, the element of the C5 cell will show up as the first unique value. You can use other VBA codes also if you want to get unique values with excel features, check this article- https://www.exceldemy.com/excel-find-unique-values-in-column/
Hope this will help you!
Hello, KRISTIN!
I’m sorry to say that, it won’t work for linking a cell color to a different sheets.
Even we can not do that actually. But you can copy the color format from a sheet cell and paste that into the differnet sheet.
Hello, BOB MARTRAY!
Thanks for noticing.
The formula is now updated!
Hello, Robyn! You can copy the formula into the cell where you need it.
Hello, JEFF WATKINS!
Yeah! It’s a bad practice, I know!
I will further keep that in mind.
That’s great, you noticed and explain this more specifically.
Thank you so much!
Hello, TREY!
Try to do it in a new worksheet and go to the Visual Basic Application using the Developer tab instead of the View Code option.
If it does not work!
Please mail me the dataset.
[email protected]
Hello, MICHAEL!
https://www.exceldemy.com/excel-dependent-drop-down-list-multiple-words/
Please, check this article!