Name: VBA Run-Time Error '9' Subscript out of range @EverydayVBAExcelTraining
Uploaded: 2024-04-30T08:17:00-05:00
Duration: 8 min 17 s
Description: VBA Run-Time Error '9' Subscript out of range

VBA Run-Time Error '9' Subscript out of range

EverydayVBA 2024-04-30 | VBA Run-Time Error '9' Subscript out of range

Open a Workbook and Transfer Data to a New Sheet in Excel VBA - CODE INCLUDED

EverydayVBA 2021-03-02 | Code and Downloadable File
https://chrisjterrell.com/blog/226224/openandtransfer
Free Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

This video will be looking at opening a file within Excel VBA using the file selector. Now it's super easy to do this, especially if you have the file path. It's easy to do it if you're using the file dialog. But what fun is that? So I'm going to show you how to open a file, go and grab some data from that file, add a new sheet and then take that data from the file that you just opened and put that in the original file. It is more fun, and you'll learn a little bit more in the process.

So let's dig in and get started. We have two Macros. The first shows you how to use the filedialog. We use Application.getOpenFile to get the string of the file you are looking to open. In this code, we are only showing the xlsx files because of this sting "Excel File Only, *.xlsx" and the title of the window is "Select a File to Open." The first thing we do is show you how "cancel" is handled. Clicking "Cancel returns "False." We use an if statement to exit the sub if the string is equal to "false." Next, we pick a file, open the file and then close it.

Now the magic happens. We open a workbook and get data from the first worksheet. Then we add a worksheet to the original workbook and copy the data. First, we declare objects which are going to be all my base objects. I dim my objects so they can be assigned. To do this, we need three objects that for "Thisworkbook" and we need two for the workbook we open.

To get the dialog of the file picker, we use Application.GetOpenFileName so we can select the file. We need to use an if statement that will exit the macro if the user clicks "Cancel" on the file dialog.
We do two things when we open the file, we open the file and assign it to our workbook object. Once the workbook is open, we set our range equal to the data in the first sheet of the workbook using the "CurrentRegion."

The next step is to add a new sheet for the data and rename it using a timestamp. We make the ranges the same size by using the resize function, and then we set the range on the opened workbook equal to the range on the new sheet we just added.
And we are done!!

'======CODE===========================

Sub OpenFile()

Filename = Application.GetOpenFilename("Excel File Only, *.xlsx", 1, "Select A file to Open")
Debug.Print Filename

If Filename = False Then
'Youtube doesn't like less than or greater than
Exit Sub
Else
'No Parenthesis because we are not assigning this to an obect
Workbooks.Open Filename
End If

MsgBox "This workbook has been open " & ActiveWorkbook.Name

ActiveWorkbook.Close

End Sub

Sub OpenFileMoveData()

Dim wb As Workbook 'For the Original Workbook
Dim ws As Worksheet 'For the newly Added workbook
Dim rng As Range 'For the range on the new workbook

Dim nwb As Workbook
Dim nrng As Range

Set wb = ThisWorkbook

Filename = Application.GetOpenFilename("Excel File Only, *.xlsx", 1, "Select A file to Open")
Debug.Print Filename

If Filename = False Then
Exit Sub
End If

'This uses parethesis because you are assigning the object
Set nwb = Workbooks.Open(Filename)
nrng = nwb.Sheets(1).CurrentRegion

Set ws = wb.Sheets.Add
ws.Name = Format(Date, "MM-DD-YY HH MM SS") 'Rename the Sheet to be a timestamp

Set rng = ws.Cells(1, 1).Resize(nrng.Rows.Count, nrng.Columns.Count) 'Set the range size equal to the size of the openned workbook
'show your work :)
wb.Activate
ws.Activate
rng.Select

rng = nrng 'THE MAGIC - this is equivalent to copying

End Sub

Conquering SaveAs in VBA - File Picker (Filedialog), Overwrite Files, Yes No Option - CODE INCLUDED

EverydayVBA 2021-02-23 | Conquering SaveAs in VBA - Overwrite Files, Yes No Option

**Get the Excel file here
https://chrisjterrell.com/blog/225118/saveasfiledialog
Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

First thing first, let's create a Runtime 1004. It is pain and it is very important because we will deal with it later in the video.

How do you overwrite a file using SaveAs. It is easy using "Application.DisplayAlerts = False". Using this method will allow you to save a file without the warning that asks if you want to save a file.

In the next macro, we use a file dialog to allow the user to select the file's correct file path and name. We use an if statement to check to see if the user selected Cancel. If Cancel is selected, the variable will equal False. If this happens, we exit the subprocedure. We then use the "Dir" function to see if the file exists. If the file doesn't exist, it will return a double quote, and we save the file.
However, if the file does exist, we use a message box to determine the next steps. If the user selects "Yes," we overwrite the file. If they select "No," we return the user to the filedailog. Finally, if they select "Canel," we exit the sub.

The last macro does an autosave with a timestamp. People have asked me how to do this, and this code is the solution. The complexity is stripping the destination string of its extension, adding a date and time, and then appending it. In this case, we hard code the .xlsm extension.

'----------------- CODE ----------------------
Sub Runtime1004()
'This makes a simple change to the workbook so Excel will show a Save Warning
Range("A1") = "Make change so Excel will show Save warning"

'Click NO or cancel to get a Runtime 1004
ActiveWorkbook.SaveAs ActiveWorkbook.FullName

End Sub

Sub ExcelVBACheckPath()
Range("A1") = "Make change so Excel will show Save warning"

filename = ThisWorkbook.Path
Debug.Print filename
filename = filename & "\FileWithoutWarning.xlsm"
Debug.Print filename

'Check if the file exists.
fle = Dir(filename)
If Dir(filename) [equal to] "" Then
MsgBox "File Exists"
Exit Sub
End If

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename
Application.DisplayAlerts = True
End Sub

Sub OverwriteOption()

fileDialog:

filename = Application.GetSaveAsFilename("Default Name of Saved File", _
"Excel Macro Files,*.xlsm", 1, "Save the File")
Debug.Print filename

If filename = False Then
'Senario 1 Cancel is clicked on the File Dialog
Exit Sub
End If

If Dir(filename) = "" Then
'Senerio 2 Duplicate File doesn't exist
ActiveWorkbook.SaveAs filename
Else
mbox = MsgBox("This File Already Exists. Do you want to Overwrite the file", vbYesNoCancel)
If mbox = vbYes Then
'Senerio 3 Overwrite the File - Without warnings
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename
Application.DisplayAlerts = True
ElseIf mbox = vbNo Then
'Senerio 4 Don't Overwrite - Send back to the File Dialog
GoTo fileDialog
Else
'Senerio 5 Cancel on the Message box exits sub
Exit Sub
End If

End If

End Sub

Sub SaveASwithTimestamp()

filename = ThisWorkbook.FullName

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
'Get File Extension
ext = fso.GetExtensionName(filename) 'This gets the Extension

filenameNoExt = Left(filename, Len(filename) - Len(ext) - 1)
Debug.Print filenameNoExt

dte = Format(Date, "yyyy-mm-dd")
tme = Format(Time, "hh mm.ss")

ActiveWorkbook.SaveAs filenameNoExt & " " & dte & " " & tme & ".xlsm", 52

End Sub

Conquering SaveAs in VBA - Saving in Multiple File Formats - CODE and Download Included

EverydayVBA 2021-02-16 | Conquering SaveAs in VBA - File Formats
**Get the Excel file here
https://chrisjterrell.com/blog/225109/saveasfileformats
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

This video explores best practices and hopes to shed light on some issues you will run into when using the SaveAs Method in VBA.

All the Excel SaveAs FileFormats are found here https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

Digging deeper into "SaveAs" will uncover some annoying workarounds when saving a file in a different file format.

In the first section of the code, we use the "Thisworkbook.FullName" property to get the file path as a string. This string is the location where we will save the file. Next, we turn off alerts using "Application.DisplayAlerts = False". By adding this line of code, we allow Excel to Save our file over the existing file with no problem and no warnings. To complete the basic save as we use Activeworkbook.SaveAs and add the file path we got from the "FullName" property. The easy work is complete.

In the next section, we drill into what Excel needs to save a file, the file format, and what Windows needs to reference the correct application to open the file format. In other words, Excel will save the file in a format that Excel can read, and Windows needs the appropriate file extension to make sure it is using the correct file format. In the next line of code, we will show how Windows only cares about the last extension. A file with the name "MyExcelFile.xlsm.csv" will be considered a CSV file by Windows.

At this point, we could have easily saved all the files and file types by adding the correct extensions and file formats, and we would have been successful with terribly named files. To avoid this, we use a file scripting object to identify the extension and remove it from the string we are using as the file path destination. We add the correct extension to the string and add the correct file type, and the files save as we expect, like magic. Now pat yourself on the back because you have conquered the challenge of Fileformats when using SaveAs in Excel VBA. You can save a file as the following extensions (xls,xlsx,xlsb,xlsm, and csv)

Mastering VBA Save and Close in Excel. It isn't as easy as you would think - CODE INCLUDED

EverydayVBA 2021-02-09 | Mastery of Save and Close in VBA
**Get the Excel file here
https://chrisjterrell.com/blog/224544/saveandclose
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

This video will go step by step on properly saving and closing a workbook and completely closing out of Excel. We will dive into some of the pitfalls you can run into when closing a workbook or quitting excel.

First, we go through the most simplistic way to save a file and close a VBA workbook. These two lines of code work well because we save the workbook right before it is closed, and therefore there are no Excel warnings or popups.

In the next sub, we Close the workbook and allow Excel Warnings. Excel shows a "Do you want to Save" warning so we won't lose any work. However, if the user clicks Cancel, the code keeps running, which can be problematic. Using "Exit Sub" can be a good solution right after this if it was nested in a "loop" or "If" statement.

In the third example, we introduce Application.DisplayAlerts. When Application.DisplayAlerts = False Excel turns off warnings like "Would you like to Save" or "Are you sure you want to delete this sheet". When you are using Application.DisplayAlerts = False, be careful when using close because all unsaved work will be lost forever. This sub also ends before the message box because, at that point, the workbook is closed.

In the fourth VBA macro, we use Application.Quit, which closes Excel completely. The key to this code is that you have saved the workbook with the code and neglected to save any other workbooks. Therefore you will be prompted to save any workbooks that need saving. If you hit Cancel, Excel will not close Excel, and your code will keep running.

The last macro is only for those who want to live dangerously. Using Application.Quit and Application.DisplayAlerts = False is a dangerous combination and should be with caution. When this runs, all unsaved work will be lost forever.

'=======CODE===================

Sub SaveAndClose()
Cells(13, 9) = "SAVE AND CLOSE - This text will be lost if WB isn't saved"

ThisWorkbook.Save
ThisWorkbook.Close

MsgBox "No Message box because no warning"

End Sub
Sub CloseWarning()
Cells(13, 9) = "CLOSE With WARNING - This text will be lost if WB isn't saved"

ThisWorkbook.Close

MsgBox "this will only show if you cancel a save"
Exit Sub 'if you still wanted to exit use Exit Sub
MsgBox "won't ever show"
End Sub
Sub CloseNOWarning()
Cells(13, 9) = "CLOSE With NO WARNING - This text will be lost if WB isn't saved"

Application.DisplayAlerts = False

ThisWorkbook.Close
MsgBox "won't ever show hope everything was saved"

Application.DisplayAlerts = True 'Best Practice ever False Should have a True
End Sub

Sub SaveAndQuit()
Cells(13, 9) = "SAVE AND QUIT - This text will be lost if WB isn't saved"
Call AddWorkbook

ThisWorkbook.Save
Application.Quit 'WARNING THIS WILL CLOSE ALL EXCEL FILES

' This will only show if you cancel save on another workbook
End Sub

Sub QuitNOWarning()
Cells(13, 9) = "QUIT NO WARNING - This text will be lost if WB isn't saved"
Call AddWorkbook

Application.DisplayAlerts = False
Application.Quit

'If you didn't save all your files you are toast
End Sub

Sub AddWorkbook()
'This is used to simulate losing work if you don't save
Workbooks.Add 'Openning a workbook to show the "Cancel Message"
Debug.Print ActiveWorkbook.Name
Cells(1, 1) = "Add some Text to book 1"

End Sub

VBA Chart Project - Change Chart based on Cell Background Color - Get Download File

EverydayVBA 2021-02-05 | ***Get the Downloadable Workbook here
https://chrisjterrell.com/blog/223074/chart-color-project
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

This chart project started simple enough. The goal was to change a chart's color based on the background color (or interior.color) of a cell. After doing a little research, we found out that this was a lot more difficult than expected. We had to Loop through the sheets, the chartobjects, the charts, the FullSeriesCollections, and all of the individual series. We used a For Loop, an array, and an If statement to compare the chart's value to the range value. If it was an exact match, we grabbed the cell's interior color and used a fancy mod formula to turn the color number into RGB format for the Chart (Thank you, Internet).

In the first video of the series, we looked at how the chart object. We did some digging using the locals and found out how where the axis label lived. Recording a macro was helpful along the way. In the second video, we wrote code that allowed us to change the chart's first bar. In the next video, we used a loop to change all the bars to a single color. We needed to do to make sure we had a default color for the chart.
The fourth video was code for changing the first bar to the color that was in the range from A1 to A5. After this step, we would need to compare the Axis value and the Range value with an if statement, and we would be very close to completion.

The fifth and sixth video in the series were loops that allowed us to loop through all the sheets, chartobjects, charts, fullSeriesCollections, and series within the entire workbook. Before this point, we were working on single charts.
In this video, we walk through all the code and explain why we coded this workbook the way we did. This project was fun, and I hope you think so as well.

If you like this video, please subscribe or comment below.

'===========================
'CODE - This will error out unless you have charts in your workbook. It will be easier to go to the blog link above download the file :)
'===========================

Sub ChartColor()

Dim sht As Worksheet
Dim cht As ChartObject
Dim chrt As Chart
Dim fs As FullSeriesCollection
Dim Rng As Range

Set Rng = Sheet1.Cells(1, 1).CurrentRegion

'Loop through all worksheets Module eLoopSheetAndCharts

For Each sht In Worksheets
For Each cht In sht.ChartObjects

'Loop through the FullSeriesCollections Module fLoopSeriesCollection
For c = 1 To cht.Chart.FullSeriesCollection.Count
cht.Activate
Set chrt = ActiveChart
Set fs = chrt.FullSeriesCollection

'Color All Item Module bColorAllItems
For Each itm In fs
'Grab the Axis Valuse and put them in a array for the for loop
oarray = itm.XValues
For rw = 1 To UBound(oarray)

clr = Sheet1.Cells(1, 1).Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
chrt.SeriesCollection(c).Points(rw).Format.Fill.ForeColor.RGB = RGB(r, g, b)

'Color an Item Module bColorAnItem
For Each cll In Rng
If oarray(rw) = cll Then
clr = cll.Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
chrt.SeriesCollection(c).Points(rw).Format.Fill.ForeColor.RGB = RGB(r, g, b)

End If
Next
Next
Next
Next
Next
Next

End Sub

Excel VBA VBA Grasping Row(s) and Columns(s) - CODE INCLUDED

EverydayVBA 2021-02-01 | **Get the Excel file here
https://chrisjterrell.com/blog/224019/rowscolumns
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

Excel VBA Graspoing Row(s)/Column(s) might be easier than you think once you grasp each one's purpose.

In this video, we compare the difference between rows/columns (with an "s") and row/column (without and "s"). When I first started using VBA, this was super confusing. As I grew in my mastery of VBA, the difference became more apparent. Row/column returns the index or number of the associated row or column on the Sheet. For example, row two is the first row on the Sheet, and Column two is equal to column "B" on the worksheet. Rows/columns are a reference to the parent object. Said another way, rows/columns are a subset of the existing Sheet or Range. You can do a lot with the referenced row(s)/column(s). In this video, we focus on selecting rows/columns and counting row/column
The code below is returning the count into a cell using the range.

'===================== CODE========================

Sub RowsColumns()

'Column is the property, Select is a methodSheet1.Rows(1).Select 'Rows is the property, Select is a method
Sheet1.Columns(1).Select 'reference the index one row at a time
Sheet1.Columns("A:J").Select 'reference multiple columns

Sheet1.Rows(1).Select 'reference the index one row at a time
Sheet1.Rows("1:10").Select 'reference multiple columns

Range("B20").Select
Range("B19") = ActiveCell.Address & " Active Cell"
Range("B20") = ActiveCell.Row
Range("B20") = ActiveCell.Column

Range("B19").CurrentRegion.ClearContents

Range("B20:G30").Select
Range("B19") = Selection.Address & " Selected Range"
Range("B20") = Range("B20:G30").Row & " Row in Range"
Range("B20") = Range("B20:G30").Column & " Column In Range"

Range("B19").CurrentRegion.ClearContents
Range("B20") = Range("B20:G30").Rows.Count & " Rows"
Range("B20") = Range("B20:G30").Columns.Count & " Columns"

Range("B20:G30").Rows(2).Select
Range("B20:G30").Columns(2).Select

End Sub

VBA Chart Project - Loop Through a FullSeriesCollecion on a Chart (Code Included)

EverydayVBA 2021-01-29 | This video will show you how to Loop through the fullseriescollection. The first thing you need to do is you need to declare three objects the chartobject, the chart, and the fullseriescollection. Then we set the chart object equal to a chart with at least two series collections. A series collection is equal to an individual column of data being graphed on the chart, and the fullseriescollection contains each series or column. This is similar to the way a workbook contains individual worksheets.

Once the ChartObject is set, we activate the Chart Object, which allows us to set the Chart equal to the Active Chart within the active chart object.

Finally, we loop through each series using a For Next Loop. Within the loop, we use Msgbox to verify the loop is looping through each series.

'===================
'CODE
'===================

Sub LoopSeriesCollection()

Dim cht As ChartObject
Dim chrt As Chart
Dim fs As FullSeriesCollection

Set cht = Sheet1.ChartObjects(3)
cht.Activate

Set chrt = ActiveChart
Set FS = chrt.FullSeriesCollection
For Each itm In FS
cnt = cnt + 1
MsgBox "Currently and Item " & itm.Name
Next

End Sub

Mastering Excel VBA Count Sheets, Rows, Columns and CountA Worksheet Function - CODE INCLUDED

EverydayVBA 2021-01-25 | Mastering Excel VBA Count Sheets, Rows, Columns, and Worksheet Functions

**See the Blog
https://chrisjterrell.com/blog/224034/countsheetrowscolumns

**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

In this video, we teach you how to master count within Excel VBA. Using "count" is very important in VBA because it allows you to count the number of sheets, rows, and columns. This mastery is a fundamental building block to grasping Loop in VBA. For example, the first Loop that most people learn in VBA is called a Do Loop. To use the Do Loop, it's essential that you understand how to use count because you'll be able to count the number of sheets rows columns within a sheet or range. The macro below consists of five rows of code. Feel to master this code, copy it into your VBA Editor, and see how it works.
The Maco will count the number of sheets and based on your selection. Then it counts the rows or columns within the selected range.
The last two lines of code use two count functions from excel.
Application.WorksheetFunction.CountA(Selection) will count all the nonblank cells within the range (in our case, the selected range).
Application.WorksheetFunction.Count(Selection) will only count the numbers within the selected range.

Sub CountStuff()

Cells(6, 8) = Sheets.Count
Cells(10, 8) = Selection.Rows.Count
Cells(12, 8) = Selection.Columns.Count

Cells(14, 8) = Application.WorksheetFunction.CountA(Selection)
Cells(16, 8) = Application.WorksheetFunction.Count(Selection)

End Sub

VBA Chart Project - Loop Through All Charts in a Workbook (Code Included)

EverydayVBA 2021-01-22 | This video is number five in our color chart project. In this video, we loop through every single sheet in a workbook, and we loop through every single chart. To do this, we declare a worksheet object, and we also declare a chart object. Then we use a For Loop to loop through each sheet and each chart object. To make the code easier to understand, we add some select statements. Also, we use a message box (MSGBOX) as we loop through the charts to show what chart is active.

We also show you how to change the name of a Chart in the upper left by the formula bar.

This video is perfect for someone looking at how to go through every sheet and every chart, and in the future videos, we will be showing how we put all of this together so that you can easily color a chart based on a cell color.

==========================
'CODE
==========================
Sub ScratchSheetandChartO()

Dim sht As Worksheet
Dim cht As ChartObject

For Each sht In Worksheets
sht.Select
For Each cht In sht.ChartObjects
cht.Activate
cht.Select
MsgBox "Chart " & cht.Name & " is now active"
Next
Next

End Sub

VBA Chart Project - Color Single Bar from Cells in a Range

EverydayVBA 2021-01-16 | This video is the fourth in the chart color series, where we change chart colors based on the interior color of a cell. This video walks through changing the first chart bar to the interior color for all the cells in column A. We use a For Loop to loop through the range in column A and change the color on our chart's first bar.

For this macro, we use three objects, a chartobject, chart, and a range object. First, we set our chartobject and our chart. For our range object, we use the currentregion to define the range in column A. Once we have identified the range, we loop through each cell within the range with a For Loop. Within the loop, we have a select statement that helps identify what cell the loop is on. Use this as a guide if you are newer to VBA. As we iterate through our range, we use clr as our color variable and make it equal to the interior.color of the cell. We again use the MOD function to get RGB and use our three variables r, g, b.

To simplify the code, we hard code our color change the first bar in the first chart (chrt.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB). We did this to make learning the VBA easier. In future videos, we will use variables to make this more dynamic.

The last thing that we do within our loop is to add a message box that displays the RGB colors we used. The message box allows us to see the color change through each iteration through the range. As we run the code, you can see that the first bar changes to every color, and then a message box will display the RGB numbers for the code.

'============================
'CODE
'============================

Sub ScratchColorTable()

Dim cht As ChartObject
Dim chrt As Chart
Dim Rng As Range

Set cht = Sheet1.ChartObjects(1)
Set chrt = cht.Chart

Set Rng = Sheet1.Cells(1, 1).CurrentRegion

For Each cll In Rng
Rng.Select
clr = cll.Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
chrt.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(r, g, b)
cht.Activate
MsgBox "Color for Selected Cells is R-" & r & " G-" & g & " B-" & b
Next

End Sub

3 Ways to Get a Sheet Name and Rename a Sheet Using VBA (Code Included)

EverydayVBA 2021-01-13 | **For more Topics Like this
https://chrisjterrell.com/blog/223404/sheetnamerenamesheets
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

In this video, we show you how to get the name of a sheet using three ways. We get the name using the Object Name or Code Name. Then we use the name of the sheet as a reference to get the index of the sheet. The sheet index is the value that represents the number sheet number from left to right in a workbook. Keep in mind that hidden sheets have an index. Additionally, reordering sheets reorder changes the index. Lastly, we get the CodeName or Object Name using the Sheet Index.

Then we rename each sheet using the three options above. We rename the first sheet using the index, we rename the second sheet by referencing the sheet name, and we rename the last sheet using the object reference.

Then we run our original macro, and it produces expected errors when referencing the sheet names because they have now changed.

‘======================
‘=======CODE==========
‘======================

Sub SheetName()

Range("I12") = Sheet1.Name
Range("I13") = Sheet3.Name
Range("I14") = Sheet2.Name

Range("J12") = Sheets("Sheet1 Name").Index
Range("J13") = Sheets("Sheet3 Name").Index
Range("J14") = Sheets("Sheet2 Name").Index

Range("K12") = Sheets(1).CodeName
Range("K13") = Sheets(2).CodeName
Range("K14") = Sheets(3).CodeName

End Sub

Sub SheetRename()

Sheets(1).Name = "By Index"
Sheets("Sheet3 Name").Name = "By Name"
Sheet2.Name = "By Ojbect-CodeName"

End Sub

VBA Active Workbook, This Workbook, Name, Path and Renaming a Workbook (Code Included)

EverydayVBA 2021-01-11 | **Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

Grasping how the Activeworkbook and Thisworkbook work will help you gain confidence as you learn VBA. The active workbook is the workbook on top of all the other windows. In other words, the last Excel window or the current Excel window open is the active workbook. This workbook, on the other hand, is the workbook that is currently running the code. The Activeworkbook may change, and Thisworkbook will always be the Object running the code.

This video uses the Thisworkbook and Activeworkbook to display the "Name" of the workbook. We also pull the file folder or path for both this workbook and the active workbook. The last thing we do use FullName to get the full file path and name of the file.

In VBA, you cannot rename workbooks like you can with worksheets because it is the file's actual file path. So to rename a file, you need to use SaveAs to "rename" the workbook. In our example, we use the current path and use "&" to concatenate some text for the SaveAs method.

‘===================
‘======CODE========
‘===================

Sub WorkbookName()
Range("h10") = ThisWorkbook.Name
Range("h11") = ActiveWorkbook.Name

Range("h13") = ThisWorkbook.Path
Range("h14") = ActiveWorkbook.Path
Range("h16") = ThisWorkbook.FullName
Range("h17") = ActiveWorkbook.FullName
ThisWorkbook.SaveAs ThisWorkbook.Path & "New WB"
End Sub

VBA Chart Project - Color All Chart based on Cell Color (Code Included)

EverydayVBA 2021-01-08 | **Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Next Video: https://youtu.be/MBsgYT7k8Io

This video is the third video in our Color Chart series, where we go through and alter a chart based on the background colors of a range of cells. To do this, we use the interior color or background color of a cell. First, we declare an object as a chart, and then we set that object equal to the first chart in sheet1. Next, we grab the color from cell one or A1 on sheet1. The interior color of a cell is a long integer that we will convert to RGB.

We take that long integer, and we make a variable called clr equal to the interior color. Then we use the MOD function to convert the long number into its RGB and make variables r, g, and b equal to the appropriate 0 to 255 number. Next, we use a for loop to loop through all the points in the first series collection (chrt.SeriesCollection(1)). The Series collections represent the number of columns that the chart is graphing. In this example, only one of the charts has two Series Collections, the chart with both the vehicle count and the % top 15. Our for loop goes from the 1 to the count of the points in SeriesCollection(1). Every iteration of the loop, we changed the point to the same color background color in Cell A1. When the loop finishes, the entire chart will be the same color.

'========================
'CODE
'=========================

Sub ColorAllItems()

Dim chrt As Chart
Set chrt = Sheet1.ChartObjects(1).Chart

'We grab the color of the A1 on sheet 1
' then convert the color to RGB which is required for charts
clr = Sheet1.Cells(1, 1).Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
'The loop below loops through the all Axis points

For c = 1 To chrt.SeriesCollection(1).Points.Count
chrt.SeriesCollection(1).Points(c).Format.Fill.ForeColor.RGB = RGB(r, g, b)
Next
End Sub

Excel VBA - ThisWorkbook Introduction (Code Included)

EverydayVBA 2021-01-04 | **Check out the BLOG
https://chrisjterrell.com/blog/223147/thisworkbook
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

In this video, we look at how ThisWorkbook works in VBA. Although this is similar to the ActiveWorkbook, This workbook is always the workbook that is currently running code. You can write a macro that opens another workbook, and when the workbook opens, the ActiveWorkbook is the workbook that was opened, and ThisWorkbook is the original workbook with the running macro. Additionally, if you have an Addin, it will never be the ActiveWorkbook, and you will always need to reference ThisWorkbook.
I this video, we have code that shows the name (ThisWorkbook.name), the FileFormat, the Path, and the FullName (the Path plus the name of the workbook)
Finally, we go into the Thisworkbook code window to write three event procedures that trigger when a workbook opens, before it closes, and before it saves. These are simple message boxes, and hopefully, you will see the potential of how these can be used powerfully for your code.

'====================
'====Module Code======
'====================
Sub ThisWorkbookIntro()

'Properties
MsgBox ThisWorkbook.Name
MsgBox ThisWorkbook.FileFormat
MsgBox ThisWorkbook.Path
MsgBox ThisWorkbook.FullName

'Methods
ThisWorkbook.Save
'ThisWorkbook.SaveAs ThisWorkbook.Path & "/Save As file", 52

End Sub
'====================
'==Thisworkbook Code==
'====================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Run Before Close"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Run Before Save"
End Sub

Private Sub Workbook_Open()
MsgBox "Run on Open"
End Sub

VBA Chart Project - Color a Bar based on Cell Color (Code Included)

EverydayVBA 2021-01-01 | ***Get the Downloadable Workbook here
https://chrisjterrell.com/blog/223074/chart-color-project
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

This video is number two in a series where we are learning how to color a bar in a chart based on the background color of a cell. Specifically, we set the color of a chart bar using the background color (or interior.color) of cell A1.

The first thing that we do as we declare a chart object. Then we activate the first chart on the sheet and set the chart object equal to the activesheet. We add a select statement so you can see which cell we are using to get the pulling the background color. Then we add a message box that shows you what that interior color number. In VBA, the background color if found suing interior.color.

In researching this project, we found that coloring a chart bar required converting the color number into RGB. After doing some Googling, if found the way to pull RGG from a color number using a MOD formula. The RGB section uses the Mod function to pass in the appropriate colors to the variables r, g, and b.

Once we had the RGB colors, we needed to find the syntax for changing the bar's color. Then we were able to pass change the bar's color to the interior color of cell A1.

'CODE
=======================

Sub ColorAnItem()

Dim chrt As Chart

Sheet1.ChartObjects(1).Activate
Set chrt = ActiveChart

'We grab the color of the A1 on sheet 1
' then convert the color to RGB which is required for charts
Sheet1.Cells(1, 1).Select
MsgBox "We are selecting the color from Cells(1,1) which is " & Sheet1.Cells(1, 1).Interior.Color
clr = Sheet1.Cells(1, 1).Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
chrt.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(r, g, b)

End Sub

VBA Chart Project - Change Bar and Column Color

EverydayVBA 2020-09-15 | **Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
**Get the file download here
https://chrisjterrell.com/blog/223074/chart-color-project

In this project, we will build out a macro that will change the color of a Bar or a Column based on a Cell Color that matches the name in the Axis. In this video, we show how the code will work and do some drilling in the Locals window to explore the ChartObject, FullSeriesCollection, Xvalue, and Points.

In the next videos, we will loop through all the sheets, all the charts, and all the FullSeriesCollections.

This ONE WILL BE FUN

Excel VBA Concatenate Function - The Reverse of Text to Columns - Code and File Included

EverydayVBA 2020-08-11 | **Get the Excel file here
https://chrisjterrell.com/blog/215568/vba-concatenate
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

Excel VBA Concatenate Function - The Reverse of Text to Columns
Concatenation is joining two strings together into a single string. A simple formula in a cell can do this, and it would look like this.

Similarly, you can concatenate strings in VBA. The example below makes s equal to "This is Text " and adds whatever is in Cell(1,1) or A1 on the activesheet

s = "This is Text " & Cell(1,1)

While this is all well and good but we can do better. Excel has a feature called "Text to Columns," but it doesn't have a function that does the opposite.
This code below allows you to reverse the "Text to Columns" in a function.

NOTE: A VBA function works just like other Excel Functions but with a couple of important caveats. The first caveat is that the function has to in the workbook. In other words, if you have a function in Book1, it will not work in Book2 unless the Function Code is copied to Book2. The second caveat is that Macros will need to be enabled for the function to work. All functions are static unless changed so that it might display the correct info, but if macros are disabled, it will not recalculate.

The Code
===========================
Function concatRange(rng As Range, delimit As String)
c = ""
For Each cl In rng
c = c & cl & delimit
Next
concatRange = Trim(c)
End Function

Excel VBA Snap Chart to Range - Resize a Chart to Match the Exactly Match Cells

EverydayVBA 2020-08-04 | Get the Excel file here
https://chrisjterrell.com/blog/215483/snap-chart

Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

Are you annoyed with Charts that won't fit within the cells? This little bit of code can be a lifesaver. I highly recommend you add this code to your Personal workbook.

In this video, we provide code that will allow you to select a chart and then select a range from an Application InputBox and the code will resize the chart to exactly match the selected cell range.

The Code:

Sub SnapChart()
On Error Resume Next
Dim chrt As Chart
Dim rng As Range

Set chrt = ActiveChart
'An Application inputbox can return ranges
Set rng = Application.InputBox("Select Range", "Selection", Type:=8)

chrt.Parent.Top = rng.Top
chrt.Parent.Left = rng.Left
chrt.Parent.Height = rng.Height
chrt.Parent.Width = rng.Width

End Sub

VBA - Looping through a Table (ListObject) and deleting a row

EverydayVBA 2020-07-02 | **Get the Excel file here
https://chrisjterrell.com/blog/213103/listobject-loop
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

Looping through a Table (ListObject) in Excel is slightly different than a sheet using "Cells" and arrays using the index in the parenthesis array(x,y). If you are using Tables in Excel, it is a brilliant move. Linking pivot tables to the table is fantastic because you don't have to resize your Pivot if your data grows or shrinks.
However, if you have coded Pivot you may have found them cumbersome. A great resource for coding ListObjects at this link here. In this link, "TheSpreadsheetGuru" gives you more than enough info to learn how to code ListObject

The Key to looping through an Object is to know its name or index. Which is similar to how you would code a Sheet and Cell. The second thing you need to know is the HeaderRowRange and the DatabodyRange because that is how you will loop through the ListObject

CODE
'A great blog post on https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

Sub listobjLoop()
Application.ScreenUpdating = False

Dim lo As ListObject
Set lo = Sheet1.ListObjects("Table1")

For cl = 1 To lo.HeaderRowRange.Count
If lo.HeaderRowRange(cl) = "weight" Then Exit For
Next

num = 2500

For rw = lo.DataBodyRange.Rows.Count To 1 Step -1
If lo.DataBodyRange(rw, cl) LT num Then
lo.ListRows(rw).Delete
End If

Next

Application.ScreenUpdating = True
End Sub

Beginning VBA - Recording Macros and VBE - Watch in Real Time

EverydayVBA 2020-03-21 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
https://chrisjterrell.com/blog/203666/recording-macros-and-visual-basic-editor

Big Challenge:
When recording a macro where does the code go and how does it work. Is it possible to see my recording in action? Watch this video to see how.

The Solution:
Record a Macro and Watch what happens in real-time. Then you can see what happens in the Project and Property window

Opening the Visual Basic Editor (3 Ways):
* Alt + F11
* Developer Ribbon - Visual Basic
* View - Macros - Select Macro - Edit

Split Your Screens so you have both Excel and the VBE open
* Record Macro - Either Using Button on the bottom left corner, the View tab - Macros or Developer tab - Record Macro
* Name your Macro - Remember No Spaces are allowed
* Find the Module in your VBE editor with the Macro you just created and watch as you record
* NOTE: remember the Macro Recorder is very literal and it will record some code that can be deleted.

The Visual Basic Editor or VBE
* Project window - Shows all open Excel Workbook
** If you see the "Personal.xlsb" file is a workbook that is open but you can't see it. It is pretty cool because you can write code that is saved there that you can run on open workbooks
* Properties - Shows the properties of whatever is selected in the Project window. So if a sheet is selected then the sheet properties will be visible.
* Code Window - where the code is written and saved
* Immediate Window - More info Here
* Locals Window - Shows the Variables during run time. It is great for debugging code

Comparing two Ranges with SumProduct in Excel

EverydayVBA 2020-02-26 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Can you use the Sumproduct is a countif function comparing ranges. YES!!!

The Challenge: A table of data and you need to compare the to a single baseline column. Put another way you want to count how many cells are greater or less than the amount in the base column.

Here is how it is done if your base was column was in Range C:10:C24 (Don't forget to click F4 to lock your column) and your comparison columns are in D and E
=SUMPRODUCT(($C$10:$C$24 GT D10:D24)*1)

How does this work? The comparison turns the array range into a Boolean.

Now we multiply that by 1 and it converts to 1's and 0's which we can add (or go to the next level and multiply another range by adding it to "Array2". I dare you!)

And there you have it!

Get more here - https://chrisjterrell.com/blog/200523/sumproduct-count
Free VBA Quick Reference Guide - https://www.chrisjterrell.com/excel-vba-quick-reference-guide//

Delete rows based on Cell Value using arrays in VBA

EverydayVBA 2020-02-06 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Delete Rows using an array
As in most coding languages, there are many ways to do something. In this video, we delete rows using an array. There are other methods to remove rows that are more efficient but you won't learn as much code!

In this video you will learn how to:

Transfer a range of data to an array using Current Region
Use an inputbox to that will be used as a criteria to remove rows
Use a For Loop to loop through the array
Use an If Statement combined with another For Loop to blank out the rows
You will then transfer the array back to the original range
And then we will sort the range to move all the zeroed out rows to the bottom
And to finish is off we will add some error handling
Below is the finalized code

Get the Code and Download the file here
https://chrisjterrell.com/blog/198241/delete-rows-using-array

Use a For Loop and If Statement to loop through an Array and add data to another Sheet in VBA Code

EverydayVBA 2019-11-06 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Master this process and you will will quickly become an Excel VBA Master.

Move data from one sheet to another using
CurrentRegion
Arrays
For Next Loops
Ubound
If Statement using criteria from an Input Box
And clearing a reporting sheet using a CurrentRegion.Offset(1,0).clear

This solution moves data from a master data set in Sheet1 to Sheet2.
We use CurrentRegion to take data from sheet one and put it into an array we then loop through that array with a for next loop using the Ubound of the array. Then to make it more fun we add an If statement to find specific rows that we want to move to Sheet2 based data from an input box. To do this we use another for loop and Ubound of the second index of the array (number of columns in the array). We delete the data at the beginning of the code and ad a button.

Code:
Sub LoopArray()
'sheet2.cells(1,1).currentregion.offset(1,0).clear

ibox = InputBox("Enter MPG over X", "MPG")

oarray = Sheet1.Cells(1, 1).CurrentRegion
rprw = 2
For rw = 2 To UBound(oarray)
If oarray(rw, 1) "GT" ibox Then
'Stop
For cl = 1 To UBound(oarray, 2)
Sheet2.Cells(rprw, cl) = oarray(rw, cl)
Next
rprw = rprw + 1
End If
Next

sheet2.select

End Sub

Knime - Read a Sheet, All Sheets in a Workbook, and All Workbooks in a folder.

EverydayVBA 2019-10-29 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video review how to read an individual sheet, all the sheets within a workbook and all the workbook and all the sheets within a folder

This workflow reads the data converts a string into a date using the "String to Date&Time" Node then uses "Extract Date&Time Fields" Node to pull the month and finally uses the "GroupBy" Node to summarize the Revenue by month

For the two loops the "Table Row to Variable Loop" to convert the rows into Variables and then loop through them. The variables are passed to the Reader and used to Set the Sheet and File Name

Excel Reader:
https://www.knime.com/knime-introductory-course/chapter2/section1/excel-reader-node

Group By:
https://www.knime.com/knime-introductory-course/chapter3/section2/classic-aggregations-with-groupby-node

Flow Variables:
https://www.knime.com/knime-introductory-course/chapter7/section1/creation-and-usage-of-flow-variables

Loops:
https://www.knime.com/knime-introductory-course/chapter7/section2

Move a Table of Data into an Array using currentregion then pasting it into a sheet using VBA

EverydayVBA 2019-10-24 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Code Included

In this video we show how easy it is to assign the currentregion which can be a table of data or a range of data (similar to using ctrl + a) using Excel VBA. Arrays are signicantly efficient in your code and this method is the easiest way and quickest way to populate an array

Code
'For this code to work you must have a two sheets one with the code name sheet1 and a second sheet with the code name of sheet2

Sub CurrentRegionArray()
'current region is like using Ctrl + A
Sheet1.Cells(1, 1).CurrentRegion.Select

oarray = Sheet1.Cells(1, 1).CurrentRegion
'The select below isn't needed but makes it easier to understand

Sheet2.Select
Sheet2.Cells(1, 1).Resize(UBound(oarray, 1), UBound(oarray, 2)) = oarray

End Sub

Split a string into array and paste into a sheet in Excel VBA

EverydayVBA 2019-10-21 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
The Split Function in VBA will split a string into a one-dimensional array. The text will be split based on the Delimiter. It is used to specify where the text should be split. In the code example below the text string is split with a space. Then we use the Ubound of the Array to resize the range to make it the same size and then make the range equal to the oarray.

Code

Sub SplitArray()
cells(1,1) = "How do I split an array"

oarray = Split(Cells(1, 1), " ")

Cells(2, 1).Resize(, UBound(oarray) + 1) = oarray

End Sub

Transfering a Worksheet Range into an Array and back to the sheet in Excel VBA- Code included

EverydayVBA 2019-10-09 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
This video shows how to easily copy or transfer data from a worksheet range into a VBA array and how to transfer or past it back on the sheet.

When working with large data sets this is the preferred method because it is a lot faster. All the calculations and work is done in memory instead of doing it on the screen.

Code

Sub Range_to_Array()
Range("A1:i1").Select
oarray = Range("A1:i1")

oarray(1, 1) = "New MPG"
oarray(1, 9) = "Car"

Range("A1:i1") = oarray

End Sub

Arrays Ubound and Lbound Explained in Excel VBA - Code Inlcuded

EverydayVBA 2019-10-05 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video we explain the Ubound (Upper bound) and LBound (Lower bound). These are used to identify the size or dimensions of an array. They return an integer or index of a particular dimension of an array. In this video we show examples of how to use the Ubound ad Lbound on a 1 and 2 dimensional array

NOTE: Debug.print displays or prints the data in the immediate window. To show this the immediate window go to View - immediate window

Code included

Sub array_code()

Dim oarray(0 To 3)
oarray(1) = "This"
oarray(2) = "is"
oarray(3) = "An Array"

'Dim earray(1 To 3)
'ReDim earray(1 To 4)
Dim darray()

ReDim darray(1 To 3)
darray(1) = "Test"

ReDim Preserve darray(1 To 1)

ReDim darray(1 To 1)
Dim uarray(1 To 20, 1 To 10)
Dim larray(10 To 20, 5 To 10)

Debug.Print UBound(uarray)
Debug.Print UBound(uarray, 2)
Debug.Print LBound(larray, 1)
Debug.Print LBound(larray, 2)
Debug.Print UBound(oarray)
Debug.Print LBound(oarray)

End Sub

Redim and Redim Preserve Dynamic Arrays Explained in Excel VBA - Code Included

EverydayVBA 2019-09-27 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video we go over how to use ReDim and Redim Preserve.

Redim is a way to resize a Dynamic array and delete the contents. Redim Preserve will resize dynamic arrays and keep the data.

Dynamic Arrays are different than fixt arrays because they are not defined when they are declared

NOTE: Fixed arrays cannot be ReDimed

CODE:

Sub array_code()

Dim oarray(0 To 3)
oarray(1) = "This"
oarray(2) = "is"
oarray(3) = "An Array"

'Dim earray(1 To 3)
'ReDim earray(1 To 4)

Dim darray()
ReDim darray(1 To 3)
darray(1) = "Test"
ReDim Preserve darray(1 To 1)
ReDim darray(1 To 1)

End Sub

What is an Array in Excel VBA (Dim a 1 and 2 Dimensional Array) - Code Included

EverydayVBA 2019-09-25 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video, you will see how to declare an array by using DIM and we set the dimensions of a one dimensional and two-dimensional array. We also show how to add info into the array using the index of the array.

What are Arrays in Excel VBA?
An array is a collection, container, or group of variables.

How do you declare arrays in VBA? See Code below

Sub array_code()

Dim oarray(1 To 3)
oarray(1) = "This"
oarray(2) = "is"
oarray(3) = "An Array"

Dim tarray(1 To 3, 1 To 3)
tarray(1, 1) = "This"
tarray(2, 1) = "is"
tarray(3, 1) = "A two Dimensional Array"

tarray(1, 1) = "This"
tarray(1, 2) = "is"
tarray(1, 3) = "A two Dimensional Array"

End Sub

Knime - Selenium Nodes Navigate to Multiple URLs

EverydayVBA 2019-07-31 | ...

Automate Excel Addin

EverydayVBA 2019-07-29 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Check out this Really cool Addin from AutomateExcel.com
*Developer (Recommended) - http://chrisjterrell.com/developer
*Beginner Link - http://chrisjterrell.com/beginner

Knime Split data into multiple sheets

EverydayVBA 2019-07-02 | In this video we use Knime to split a dataset into multiple Excel Sheet. This is almost too easy. And yes this template preloaded into Knime.

Knime - Combining Multiple Sheets into a One Sheet

EverydayVBA 2019-06-01 | Using a Data Analysis tool called Knime we easily combine all the sheets from a workbook into a single data set

VBA Find Last Row on a Sheet, in a Range, or Table - Weekend Event

EverydayVBA 2019-01-12 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
This is your time to ask me question as we go through this topic

In this video we will go over all the ways to find the last Row on a Sheet, in a Range or in a Table. We will explore xldown, xlup, current region and usedrange.

EverydayVBA Live Stream

EverydayVBA 2019-01-03 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/

Vlookup in VBA - Live Weekend Workshop

EverydayVBA 2018-12-29 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Hi All, I keep getting requests for help so I thought I would start doing some weekend workshops.

These will be an open forum for questions and challenges. We can go over VBA, Excel, Google Sheets. SQL, or anthing else that comes up

Sub Vlookup()

Dim rng As Range

Columns("E:Z").Columns("E:Z").ClearFormats

Set rng = Range(Range("b6").Value)

rng.Interior.Color = Range("c6").Interior.Color

For Each cl In rng
If cl.Column = rng.Cells(1, 1).Column Then
cl.Interior.Color = Range("C5").Interior.Color

If cl.Value = Range("B5") Then

cl.Resize(1, Range("b7")).Interior.Color = Range("C7").Interior.Color
Range("b10") = cl.Offset(0, Range("b7") - 1)

Stop

End If

End If

Next

End Sub

Highlight Duplicates in Excel and Google Sheets

EverydayVBA 2018-12-29 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
How do you highlight duplicates within a column in both Excel and Google sheets using conditional formating.

Excel already has a this built in. Conditional Formating - Highlight Cell Rules - Duplicate values

Google sheets requires a custom formula like =Countif(A:A,A1) greater than 1 (I can't actually use the greater than sign in this area)

EverydayVBA Challenge Solution: Splash Screen

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
VBA

EverydayVBA Personal Workboook Setup and Recording Macros

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
We Record a Macro to add the Personal.XLSB workbook

Excel VBA - Visual Basic Editor or VBE

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this Video we do an overview of the the Visual Basic Editor

Excel VBA - Macro Settings

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
Excel Options Trust Center Settings

Excel VBA Use a Do Loop to populated another sheet

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video we use a do loop to to loop through a set of data.

Excel VBA Use a Do Loop to popluated another sheet Video2

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video we use a do loop to to loop through a set of data.

Excel VBA - Use a Do Loop to popluated another sheet Video3

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video we use a do loop to to loop through a set of data.

VBA Project Use a Do Loop to Popluate another sheet

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
In this video we use a do loop to to loop through a set of data.

Macro Monday Solution Message Box

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
The Solution to the Macro Monday Message Box Challenge

Excel VBA Coding the SUM Function as a Macro

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
This is the intro to the Sum Function macro.

Excel VBA Sum Function as a Macro Set Up

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
This is the Set up Video for createing the Sum Function Macro

Excel VBA Splash Screen

EverydayVBA 2018-12-28 | Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-vba-quick-reference-guide/
VBA Tutorial on how to make a splash screen for Excel.

9 lines of code that will blow their socks off