User Posts: Sabrina Ayon
5

## How to Copy a Formula Across Multiple Rows in Excel (5 Ways)

In Microsoft Excel, we may occasionally have to repeat the same formula to a whole column or row. If we enter the formula in each cell one by one, it will take ...

6

## How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)

Copying and Pasting are useful because they allow transferring data from one place to another without retyping it each time. In Microsoft Excel, the maximum ...

7

## Excel VBA to Copy Data from Another Workbook without Opening

In Microsoft Excel, VBA Macros can easily solve a wide range of problems. If we want to copy data from another workbook without opening the workbook, we can ...

5

## How to Convert Degrees Decimal Minutes to Decimal Degrees in Excel

Degrees, minutes, and seconds (DMS) are often used to indicate the Angular Measurements. In this article, we will learn how to convert the degrees decimal ...

6

## How to Create Quarterly Compound Interest Calculator in Excel

Compound Interest is a crucial part of financial computation that so many of us utilize on a regular basis. Excel has a financial function to calculate the ...

6

## How to Calculate Cycle Time in Excel (7 Examples)

In our daily life, time is a precious commodity. A company’s capability of creation, growth, expectations, and maximum things are closely linked to Time. We ...

5

## How to Calculate Gross Profit Margin Percentage with Formula in Excel

If you need to calculate gross profit margin percentage, you can easily do it in excel. Here, we can use a very easy and simple formula to compute the gross ...

3

## How to Calculate Interest Rate on a Loan in Excel (2 Criteria)

In Microsoft Excel, we can do the financial calculations quickly, because excel has some built-in financial functions. For example, suppose you want to get a ...

5

## How to Calculate Average of Multiple Ranges in Excel (3 Methods)

The average is calculated by summing the specified numbers and dividing them by the total values selected. We use averages because it’s beneficial to contrast ...

5

## How to Link Files in Excel (5 Different Approaches)

While working with Excel, sometimes we need some reference data from other link files. It’s a usual use case that’s also rather simple to implement. In this ...

4

## Keyboard Shortcut to Freeze Panes in Excel (3 Shortcuts)

Microsoft Excel has plenty of shortcuts. Those shortcuts help us to do work faster and also save time. In Excel, while scrolling over a huge dataset, sometimes ...

3

## How to Freeze Multiple Panes in Excel (4 Criteria)

It’s distinctive to freeze rows and columns in Excel while working with large datasets. As a result, while navigating to another part of the worksheet, we can ...

3

## How to Print Multiple Sheets in Excel (6 Suitable Examples)

Microsoft Excel allows us to print the data as per our wish. There are so many options available while printing multiple sheets at a time in Excel. In this ...

3

## How to Print Excel Sheet with Lines (3 Easy Ways)

When reading data on worksheets printed in Excel, lines of the row and column titles can be useful. But, those lines aren't printed on a Microsoft Excel ...

3

## How to Sort Excel Tabs in Ascending or Descending Order

In Microsoft Excel, If we wish to sort Excel tabs, there are no built-in functions or tools to do that. We can only do it manually or using macros can help. In ...

Browsing All Comments By: Sabrina Ayon
1. Reply 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

2. Reply Hello, HUNTER!
Thanks for sharing your problem with us!
To add a condition in the code from step 1 to only send once a day, you can use a global variable to keep track of the last time an email was sent. Here’s an example of how you can modify the code:

``````Private lastSentTime As Date

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if only one cell was changed
If Target.Cells.Count > 1 Then Exit Sub

'Check if the changed cell is C15
If Not Application.Intersect(Range("C15"), Target) Is Nothing Then
'Check if the new value is less than 72
If IsNumeric(Target.Value) And Target.Value < 72 Then
'Check if at least one day has passed since the last email was sent
If Now - lastSentTime >= 1 Then
'Send the email
Call Send_Email_Condition_Cell_Value_Change

'Update the last sent time
lastSentTime = Now
End If
End If
End If
End Sub``````

In this modified code, the lastSentTime variable is used to keep track of the last time an email was sent. When a cell is changed and meets the criteria for sending an email, the code checks if at least one day has passed since the last email was sent before sending a new email. If less than a day has passed, the code skips sending the email. Once an email is sent, the lastSentTime variable is updated with the current time.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

3. Reply Hello, JOHN!
I’m not sure about your problem, you want all Lenovos that means the Desktop and Notebook! Also, All the desktops that mean the range of C5:C9!
Can you please send me your excel file via email? ([email protected]).

You can use this https://www.exceldemy.com/sum-index-match-multiple-criteria-in-excel/#Use_of_SUMIFS_with_INDEX_MATCH_Functions_in_Excel

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

4. Reply Hello, AMIR!

Yes! The code you provided is a simple implementation for converting latitude and longitude to UTM in Excel VBA. However, it is not the most accurate way to convert coordinates, as it uses a simplified formula for converting latitude and longitude to UTM coordinates.

The formula used in this code only accounts for the UTM zone and hemisphere based on the latitude and longitude values. It does not take into account the curvature of the Earth’s surface, which can lead to inaccuracies in the UTM coordinates. If you need this, then you can use the following code:

``````Function LatLonToUTM(Lat As Double, Lon As Double) As String
'Declare variables for UTM coordinates
Dim Zone As Integer, Easting As Double, Northing As Double

'Convert Lat/Lon to UTM using Proj4 library
Dim Proj As Object
Set Proj = CreateObject("OSGeo.OSR.SpatialReference")
Proj.ImportFromEPSG 4326 ' WGS84
Proj.SetUTM Zone(Lon), (Lat >= 0)

Dim LatLon(0 To 1) As Double
LatLon(0) = Lon
LatLon(1) = Lat

Dim xy(0 To 1) As Double
Proj.TransformPoint xy(0), xy(1), 0, LatLon(0), LatLon(1), 0

'Convert UTM coordinates to string format
LatLonToUTM = Chr(Zone + 64) & " " & Format(Easting, "0.00") & " " & Format(Northing, "0.00")
End Function``````
``````Function Zone(Lon As Double) As Integer
Zone = Int((Lon + 180) / 6) + 1
End Function``````

To use this code, open a new Excel workbook, press ALT+F11 to open the VBA editor, and insert a new module. Copy and paste the code into the module, and save the module.
Then, in your Excel sheet, you can use the formula =LatLonToUTM(lat, lon) where lat and lon are the latitude and longitude coordinates you want to convert to UTM format.
This code uses the Proj4 library to perform the coordinate transformation. You may need to install this library on your computer if it is not already installed.
And if you don’t want to use this library. you can use the following code instead.

``````Function LatLongToUTM(ByVal latitude As Double, ByVal longitude As Double) As String

'Define constants for the conversion
Const PI As Double = 3.14159265358979
Const a As Double = 6378137
Const b As Double = 6356752.3142
Const k0 As Double = 0.9996

'Convert latitude and longitude to radians
lat_rad = latitude * PI / 180
long_rad = longitude * PI / 180

'Define constants for the conversion
Dim e As Double
e = Sqr(1 - (b / a) ^ 2)
Dim e1sq As Double
e1sq = (e ^ 2) / (1 - e ^ 2)
Dim n As Double
n = (a - b) / (a + b)
Dim rho As Double
rho = a * (1 - e ^ 2) / (1 - (e ^ 2) * (Sin(lat_rad) ^ 2)) ^ 1.5
Dim nu As Double
nu = a / Sqr(1 - (e ^ 2) * (Sin(lat_rad) ^ 2))
Dim psi As Double
psi = nu / rho
Dim v As Double
v = (k0 * nu) / psi
Dim p As Double
p = long_rad - (2 * PI * Int((long_rad + PI) / (2 * PI)))
Dim l As Double
l = (p - 0.2617993877991495) * 180 / PI

'Calculate UTM zone number
Dim zone As Integer
zone = Int((long_rad + PI) / (2 * PI)) + 1
If zone > 60 Then
zone = zone - 60
End If

'Calculate UTM x and y coordinates
Dim x As Double
Dim y As Double
x = v * Sinh(l / (2 * 1.005037))
y = v * (Cosh(l / (2 * 1.005037)) * Tanh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))) - (e * Atanh(e * Sinh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))))))))

'Convert x and y to UTM coordinates
x = x + 500000
If y  60 Then
zone = zone - 60
End If

'Calculate UTM x and y coordinates
Dim x As Double
Dim y As Double
x = v * Sinh(l / (2 * 1.005037))
y = v * (Cosh(l / (2 * 1.005037)) * Tanh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))) - (e * Atanh(e * Sinh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))))))))

'Convert x and y to UTM coordinates
x = x + 500000
If y < 0 Then
y = y + 10000000
End If

' Define UTM zone letter
Dim letters As String
letters = "CDEFGHJKLMNPQRSTUVWXX"
Dim zone_letter As String
zone_letter = Mid(letters, zone, 1)

' Return UTM coordinates as string
LatLongToUTM = zone & zone_letter & " " & Format(x, "0.00") & " " & Format(y, "0.00")

End Function``````

This code converts latitude and longitude coordinates to UTM coordinates and returns the result as a string in the format "Zone Letter X Y". You can call this function by passing the latitude and longitude values as parameters, like this:

``````Sub ConvertLatLongToUTM()
'Define latitude and longitude values
Dim latitude As Double
Dim longitude As Double
latitude = 37.7749
longitude = -122.4194

'Call LatLongToUTM function
Dim utm_coordinates As String
utm_coordinates = LatLongToUTM(latitude, longitude)

'Display UTM coordinates in message box
MsgBox utm_coordinates

End Sub``````

Make sure you have the LatLongToUTM function defined in your VBA code module before running the ConvertLatLongToUTM sub.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

5. Reply Hello, OBOT!
To import data from a specific sheet (e.g., “Sheet2“) of a Google Sheets document to Excel using VBA, you can use the following code:

``````Sub ImportDataFromGoogleSheets()

'Set the URL of the Google Sheets document

'Set the name of the sheet containing the data you want to import
Dim sheetName As String
sheetName = "Sheet2"

'Set the target range where you want to paste the imported data
Dim targetRange As Range
Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1")

'Define the source range of the data in the Google Sheets document
Dim sourceRange As Range
With CreateObject("MSXML2.XMLHTTP")
.send
Dim responseText As String
responseText = .responseText
Dim startIndex As Long
startIndex = InStr(1, responseText, sheetName & "!A1")
If startIndex > 0 Then
Dim endIndex As Long
endIndex = InStr(startIndex, responseText, "class=""") - 1
If endIndex > startIndex Then
rangeAddress = Mid(responseText, startIndex, endIndex - startIndex)
End If
End If
End With

'Copy the data from the source range to the clipboard
sourceRange.Copy

'Clear the target range to ensure that no existing data interferes with the import
targetRange.CurrentRegion.ClearContents

'Paste the data from the clipboard into the target range
targetRange.PasteSpecial xlPasteValues

End Sub``````

To use this code, you need to replace the googleSheetsURL variable with the URL of the Google Sheets document containing the data you want to import and replace the sheetName variable with the name of the sheet containing the data you want to import (in this example, “Sheet2“). You also need to set the targetRange variable to specify the cell or range where you want to paste the imported data (in this example, cell A1 of the Sheet1 worksheet).

The code uses the MSXML2.XMLHTTP object to send an HTTP request to the Google Sheets document, and parses the HTML response to identify the range of cells corresponding to the specified sheet name. It then copies the data from the identified range to the clipboard, clears the target range to ensure that no existing data interferes with the import, and pastes the data from the clipboard into the target range.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

6. Reply Hello, MRS B!
Can you please send me your excel file via email? ([email protected]), so that I can solve your problem!

Right now I’m giving you a quick solution without the dataset. You can use Excel’s VLOOKUP function to have fields in the payment request form automatically fill in depending on the employee number.

Here is a formula that uses the VLOOKUP function as an example:

`=VLOOKUP(employee number,employee table,2,FALSE)`

Here, “Employee number” refers to the cell where the employee number input is located, “Employee table” refers to the cell range containing the employee information table, which includes the employee number in the first column, and “2” refers to the column number in the table that contains the university information.

You can change this formula to return different data. Once the relevant information has been obtained from the table, you can use it to fill in the essential fields on the payment request form by utilizing straightforward cell references or other procedures.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

7. Reply Hello, UDAY KUMAR!
It seems that the error message you received is related to the use of reference operators in the formula.
The formula you were using in the conditional formatting rule contains a reference operator and the INDEX function, which can be interpreted as an array constant. The error message you received indicates that the use of such operators and array constants is not allowed in conditional formatting criteria.

To avoid using the reference operator and array constant, you can use the INDIRECT function. Here’s an example formula that uses the INDIRECT function:

`=SUM(INDIRECT("B\$3:B" & ROW()))<=\$B\$35`

The INDIRECT function takes a text string argument that specifies a cell reference and returns the value of the cell. By concatenating the starting and ending cell references with the ROW function, we can create a dynamic reference to the range we want to sum.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

8. Reply Hello, EHTISHAM SAFDAR!
Thanks a ton for your suggestion!
In Method-7, the precise range we require to count the number of cells containing dates is D5:D12. Determines whether each data value in a given array or range is legitimate by SUM each one.

Regards,
Sabrina Ayon
Author, ExcelDemy
.

9. Reply Hello, BILL SHIELDS!
Stay connected with Exceldemy.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

10. Reply Hello, OZTIMS!
Thanks for sharing your problem with us!
Instead of using this format (\$General;;), you can use the (\$0;-0;;@). This will keep the negative values. To use this follow method-4.

1. When the Format Cells dialog box will appear, go to Number > Custom.
2. Type \$0;-0;;@ in the Type field.
3. Finally, click OK. 4. Now, if you see the result, this will also show a negative number. The cell will only be blank if the cell has no data. Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

11. Reply Hello, Billy!
Thanks for sharing your problem with us!
Actually, this code perfectly works for me. This code extracts specific data from pdf to Excel properly. Please, make sure you use the accurate Application and PDF paths.
Can you please send me your excel file via email? ([email protected]).
So that, I can solve your problem.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

12. Reply Hello, DONI!
Thanks for sharing your problem with us!
All the methods work properly for me. I am also using Microsoft Office 365.
Can you please send me your excel file via email? ([email protected]).
So that, I can solve your problem.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

13. Reply Hello, Bob!
Yes! This formula won’t work in Excel 2016. I will suggest that use Excel 365.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

14. Reply Hello, JHORDISTA!
Thanks for your comment. To format a negative number showing parenthesis, you can add this block of code with any of the above VBA code.

``````Sub Negative_Numbers()
Range("A1:A10").Select
Selection.NumberFormat = "0.00_);[Red](0.00)"
End Sub``````

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

15. Reply Hello, DANIEL DUMITRU!
Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

16. Reply Sabrina Ayon Nov 6, 2022 at 2:56 PM

Hello, ASHLEY!
Yes. Unfortunately, the google charts API is currently broken. You can use the following API which I updated in the article.

Try out this code below.

``````Function GenerateQR(qrcode_value As String)
Dim URL As String
Dim My_Cell As Range
Set My_Cell = Application.Caller
On Error Resume Next
On Error GoTo 0
ActiveSheet.Pictures.Insert(URL).Select
With Selection.ShapeRange(1)
.Name = "My_QR_CODE_" & My_Cell.Address(False, False)
.Left = My_Cell.Left + 5
.Top = My_Cell.Top + 5
End With
GenerateQR = ""
End Function``````

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

17. Reply Hello, LOCHIA!
Thanks for sharing your problem with us!
Actually, in the following formula, C stands for Column, and R stands for Row. The While loop block of codes is searching Column C in a loop for values that match. Up until there is no match, iteration continues. If no match is found, it tosses the Sum value.

`="=SUM(R" & xValue & "C:R" & iValue & "C)"`

Can you please send me your excel file via email? ([email protected]).
So that, I can solve your problem.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

18. Reply Hello, AMIRA!
Thanks for sharing your problem with us!
The code works properly for me. Can you please send me your excel file via email? ([email protected]).
So that, I can solve your problem.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

19. Reply Thanks, SR DIABLO!
Yes, you are right! Actually, the purpose of this line is the same as the loop. You can either use this block of code.

``````For bb = 1 To gg.Columns.Count
cc = cc + .Cells(1, gg.Column + bb - 1).ColumnWidth
Next bb``````

Alternatively, you can use this line instead of using the loop.

``cc = .Cells(1, gg.Column).ColumnWidth + .Cells(1, gg.Column + 1).ColumnWidth``

You can skip the line or comment on the line using an apostrophe in front of the line you wish to turn into non-executable code. It’s actually not a mistake. The code will work properly if you do not remove it! But I suggest you use any one of those.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

20. Reply Hello, GABRIEL!
Thanks for sharing your problem with us!
To add a new line you don’t have to write the command .Insert. You can simply use this block of the code to breakline in the string before cFnd.

``````xTmp = xTmp & Split(Rng.Value, cFnd)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y)
xTmp = xTmp & vbNewLine & cFnd``````

To use vbNewLine, you have to make sure to do the following.
1. After the ampersand (&) symbol, press the spacebar and get the VBA constant ‘vbNewLine‘.
2. After the constant ‘vbNewLine‘, press one more time space bar and add the ampersand (&) symbol.
3. After the second ampersand (&) symbol, type one more space character, and add the next line sentence in double-quotes.

In VBA, there are three different (constants) to add a line break.
vbNewLine, vbCrLf, vbLf

If this is not working for you, follow the steps.
1. Click on the character you wish to break the line from first.
2. Then, enter a space ( ).
3. Type an underscore (_) after that.
4. Finally, press Enter to finish the line.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

21. Reply Hello, DALE HALL!
Thanks for sharing your problem with us!
You can set a range of cells to highlight using the following VBA code.

``````Sub Highlight()
For Each cell In Range("A5:A8")
cell.EntireRow.Interior.ColorIndex = 6
Next
End Sub``````

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

22. Reply Hello, Rick!
Thanks for sharing your problem with us!
While copying, you have to copy the whole thing (values + formulas). You can have the values (without any formula) only while pasting the copied portion into another place.
While pasting, instead of using ‘.Paste‘ to replicate a formula result as a value rather than the formula itself, use ‘.PasteSpecial‘.

``````For Each cell In Selection.Columns(3).Cells
If cell.Value = “New York” Then
cell.EntireRow.Copy
Worksheets(“VBA2Copy”).Range(“A” & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
End If
Next``````

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

23. Reply Hello, Niki!
Thanks for sharing your problem with us!
You can use the formula to find the second last result from a certain cell.
For this,
1. Select the cell where you want to see the result.
2. Insert the formula into the formula bar.
`=INDEX(D:D,LARGE(IF(D:D<>"",ROW(D:D)),2))`
3. Press Shift + Ctrl + Enter. Note: You have to press Shift + Ctrl + Enter together, otherwise the formula won’t work.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

24. Reply Hello, JOHN!
You can lock the row after the date auto updates with the following code.

``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "\$A\$1" And Target.Value <> "" Then
ActiveSheet.Protect Contents:=False
ActiveSheet.Protect Contents:=True
End If
End Sub``````

A cell should only be locked if cell A1 was updated and it is not blank, according to this formula: if Target.Address = “\$A\$1” and Target.Value > “”
Just substitute the relevant cell value for \$A\$1 to make the macro function on cell B1, cell D15, or any other cell. For this to function, the column and row references must be preceded by dollar signs.
By changing > “” in the line above to = “desired value,” you may additionally lock the cell only if a certain value was entered, allowing you to do things like lock the cell only if OK was entered or anything similar.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

25. Reply Hello, DWIGHT!
Yes! You have to update the range manually in the code.

Regards,
Sabrina Ayon
Author, ExcelDemy
.

26. Reply Hello, MAC!
Thanks for sharing your problem with us!
To integrate these 2 codes, all you need to do is just define the first sub-procedure name in the second part of the code and add the sheet name there before the range-bounded combination. “Worksheet_Change Sheet1.Range(“B5”).Validation…….” like this.

The code should look like this.

``````Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 4 Then
If Target.Cells.Row = 2 Then
N = Target.Cells.Row
If Range("D" & N).Value "" Then
Range("D2").EntireRow.Insert
Range("A2").Select
End If
End If
End If

enditall:
Application.EnableEvents = True
End Sub

Sub CreateDropDownList()

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

27. Reply Hello, COLE!
Thanks for sharing your problem with us!

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

28. Reply Hello, AUSTIN!
Thanks for sharing your problem with us!

To convert values into timestamps, follow the instructions below.
1. select the cell and put the formula into that cell.
2. Press Enter.
`=(INT(B3/10000)&":"&INT(MOD(B3,10000)/100)&":"&MOD(B3,100))+0` 3. This will convert the values into time values.
4. Now, go to Home tab by selecting the resulted cell and click on Number Format drop-down menu under Number group. 5. Drag the Fill Handle icon down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol. 6. And, that’s it! But there is an issue. as 13 represent 1 in time, so 13 will replaced by 1. If not, can you please send me your excel file via email? ([email protected]).

Good Luck!
Best Regards
Sabrina Ayon
Author, ExcelDemy.

29. Reply Hello, LIZ!
Thanks for sharing your problem with us!

Excel automatically detects all difficulties when you interact with it, including inaccurate data in the cell, issues with formulae, etc. As a result, the top left corner of these cells is shown (by default) with green triangles. Excel displays green triangles, this green triangle indicates a potential mistake, although it is frequently ineffective.
Do the following to disable these green triangles or automatic calculation checks:

1. Go to the File tab from the ribbon. 2. Select the Options option from the File tab. 3. Enable background error checking is an option that may be disabled in the Excel Options dialog box’s Formulas tab’s Error Checking section.
All open workbooks in the Excel session will be affected by this application-level option. If not, can you please send me your excel file via email? ([email protected]).

Good Luck!
Best Regards
Sabrina Ayon
Author, ExcelDemy.

30. Reply Hello, JAMES RICHMOND!
Thanks for sharing your problem with us!

Good Luck!

Regards,
Sabrina Ayon,
Author, ExcelDemy.

31. Reply Hello, DANDELION!
Please select the range properly, this macro also works for more than 100 duplicates. There is no limitation. All you need to do is after running the code select the range properly.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy.

32. Reply Hello, GIA!
As you mentioned, you fill out Column C with data, and Column B will automatically update with the date when Column C was filled out. All you need to do is change the range in your code, and also change the reference argument which is the offset. Try this code below.

``````Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C5:C8"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub``````

Also, you can use the same code for column E to automatically update with the date and time when you fill Column D with “Delivered”. You just have to change the range.

``````Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D5:D8"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub``````

https://www.exceldemy.com/auto-populate-date-in-excel-when-cell-is-updated/#2_Auto_Populate_Dates_in_Some_Specific_Cells_While_Updating_with_Excel_VBA

Best Regards.

33. Reply Sabrina Ayon Oct 2, 2022 at 3:02 PM

Hello, VICKIE WATT!
Thanks for your comment. Yes, this is date static.

34. Reply Sabrina Ayon Oct 2, 2022 at 1:02 PM

You are most welcome, Wayne Edmondson!
Stay Tuned!

Regards,
Sabrina Ayon
Author, ExcelDemy.

35. Reply Sabrina Ayon Oct 2, 2022 at 1:00 PM

Hello, WAYNE EDMONDSON!
Thanks for sharing your thoughts with us!
Stay Tuned!

Regards,
Sabrina Ayon
Author, ExcelDemy.

36. Reply 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

Thanks for sharing your problem with use.

37. Reply Hello, Jim!
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!

38. Reply 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!

39. Reply 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

40. Reply 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!

41. Reply Hello, ROWAN!
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

42. Reply 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

43. Reply Sabrina Ayon Sep 7, 2022 at 9:46 AM

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!

44. Reply 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

45. Reply 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.

46. Reply 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

47. Reply Hello, THOMAS SAULNIER!

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

48. Reply Hello, DIANA!
There is no problem with your code. What’s the problem actually?
Can you please email me the dataset here; [email protected]

https://www.exceldemy.com/automatically-send-email-from-excel-based-on-date/

49. Reply 50. Reply Hello, CRISTIAN!
https://www.exceldemy.com/calculate-travel-time-between-two-cities-in-excel/

51. Reply 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.

52. Reply 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
Next WrkSht
End Sub

53. Reply Hello, JOHN!
You can run the code by pressing the keyboard shortcut F5.

54. Reply 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

55. Reply 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.

56. Reply 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.

57. Reply 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

58. Reply 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-unique-values-in-column/

59. Reply 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.

60. Reply Hello, BOB MARTRAY!
Thanks for noticing.
The formula is now updated!

61. Reply Hello, Robyn! You can copy the formula into the cell where you need it.

62. Reply 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!

63. Reply 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!
64. Reply    