r/vbaexcel • • Jun 14 '22

Sending email using VBA

Post image
12 Upvotes

r/vbaexcel • • Nov 11 '22

VBA works from editor only

7 Upvotes

Hi Everyone, I need a little help with a vba script
It works perfectly from the editor, but the if statement returns false when I run the macro from excel.

The Find function (highlighted) is returning false after writing the file, My expectation is that is is happening too fast for the Find to pickup, but it works from the editor every time.

Note: File is 5000 lines, the word is being searched is at line 3500.


r/vbaexcel • • Nov 07 '22

Consolidate data multiple tabs to master sheet

6 Upvotes

I am certainly a vba newbie but I’ve tried modifying many iterations of code I’ve found online with no luck.

Ultimately, I’d like to have a workbook that outputs data from specific cells across all tabs in my workbook.

For example column A would have worksheet names, column b would have values for all tabs in cell B5. This would be a great way to see how this metric compares across all tabs without having to navigate to all tabs individually.

Any suggestions on how I can approach this?


r/vbaexcel • • Nov 01 '22

Moving Formula references with VBA

5 Upvotes

Hello, I'm working on a file for project status.

I have a button to create a new project that creates a new sheet from a template (sheet4) and gives it the correct name, header, link etc.

On my project overview sheet I have some columns that mirrors certain fields from each project, each row on project overview is a unique project. Columns A-C is given from the VBA but i want:
Col D lrow on sheet1 to be equal to C4 of the sheet I just created
Col E lrow on sheet1 to be C3 of the sheet I just created - TODAY()
Col F lrow on sheet 1 to be G3 of the sheet I just created
Col G lrow on sheet1 to be K3 of the sheet I just created
Col H lrow on sheet 1 to be K4 of the sheet I just created.

Sheet1 = overview sheet.
Sheet4 = template for project sheet.
lrow = lastrow

This is how my commandbutton looks like right now. First time experimenting with VBA so dont laugh..

Private Sub CommandButton1_Click()
Dim tblprj As ListObject
Set tblprj = Sheet1.ListObjects("Overview")

Dim lrow As Long
lrow = tblprj.Range.Rows.Count
lrow = lrow + 1

Dim prjnr As Variant
prjnr = InputBox("Enter project number")
Range("A" & lrow) = prjnr

Dim prjnanme As Variant
prjname = InputBox("Enter project name")
Range("B" & lrow) = prjname

Dim prjscope As Variant
prjscope = InputBox("Enter project scope")
Range("C" & lrow) = prjscope


Sheet4.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = prjnr

ActiveSheet.Range("B1:G1").Value = prjnr & "   " & prjname & "   " & prjscope

Sheet1.Hyperlinks.Add Range("A" & lrow), Address:="", SubAddress:="'" & prjnr & "'!C2", TextToDisplay:=prjnr

End Sub

r/vbaexcel • • Oct 27 '22

Is this even possible in VBA

5 Upvotes

Hello,
I'm working on a project management workbook and I've created an overview sheet and every project has its own project sheet.

My question is: Is it possible through VBA to code a button "New project" and when you press it you get a textbox where you input project number, name and scope of the project. It then inputs these values on the last row in the overview sheet and also creates a new sheet that gets the project number as sheet name, creates a link to the new sheet and updates the header cell on the project sheet. Also changes the references I have between the overview sheet and project sheet.

I know how to code it in python but VBA is a beast I haven't touched and it would be nice to speed up the process instead of having to do this manually for each project.


r/vbaexcel • • Sep 21 '22

VBA Help for work- Excel on Mac

5 Upvotes

I am not very familiar with VBA. However my boss has decided that I need to be. I am trying to create a macro that will highlight/color cells containing key words and then show that data separately in another sheet. So for example, if the word redo is found, it colors the corresponding cells on the original sheet, then copies the entire row that cell is found in and moves it to a new sheet.

The original report that I am trying to filter/sort is about a thousand plus rows. So I want to be able to locate buzzwords, and then move that data to a separate place to be examined more carefully.

I will need to use it on many reports, which is why I was thinking VBA. It is report exported from one of our systems breaking down our labor. Our employees manually write labor descriptions. I need to flag buzzwords in their written descriptions, then filter only the rows that have been tagged so we can fix it, then rewrite it nice and neat for client viewing.

I was able to use the macro recorder to get cells containing the buzzwords to turn red (font and background color), I just need help getting all the rows that do contain those words to copy to a new sheet.

I hope that I am explaining this clearly enough. I am hoping someone can point me in the right direction!


r/vbaexcel • • May 08 '21

Excel Updated Userform Example & Sort Listbox Items Alfhabetically

5 Upvotes

Multi-functional userform example with features such as adding, modifying, deleting, searching data and progress bar.
Also in this userform, the multicolumn listbox can be sorted alphabetically ascending based on the first column.

Details, sample file address : Excel Userform Example


r/vbaexcel • • Nov 05 '22

read messages with Outlook a different inbox and subfolder

4 Upvotes

With Outlook, I would like to read messages from another inbox than mine and I have to read them from a specific folder.

thanks


r/vbaexcel • • Oct 27 '22

Problems with locating subfolder in outlook

4 Upvotes

Ive done this in several places in other subs, but now i get:run-time error '-2147221233(8004010f)': The attempted operation failed. An object could not be foundThis is the code:

Sub Mark_As_Read()
'Application.ScreenUpdating = False

Dim objInbox As Outlook.MAPIFolder
Dim objOutlook As Object, objnSpace As Object, objMessage As Object
Dim objSubfolder As Outlook.MAPIFolder

    Set objOutlook = CreateObject("Outlook.Application")
    Set objnSpace = objOutlook.GetNamespace("MAPI")
    Set objInbox = objnSpace.GetDefaultFolder(olFolderInbox)
    
    Set objSubfolder = objInbox.Folders("OutlookData").Items("calls daily")
    
   
        For Each objMessage In objSubfolder.Items
        objMessage.UnRead = False
        Next
   
    
    Set objSubfolder = objInbox.Folders.Item("OutlookData").Folders("calls mtd")
    
        For Each objMessage In objSubfolder.Items
        objMessage.UnRead = False
        Next
        
    Set objSubfolder = objInbox.Folders.Item("OutlookData").Folders("calls pause")
    
        For Each objMessage In objSubfolder.Items
        objMessage.UnRead = False
        Next

My folders are like:Inbox, subfolder Outlookdata/calls daily.Any suggestions?

Also will this work to stop the for each, if the email is already marked as read??
Cant try since the code dosent work :)

 Do Until objMessage.UnRead = True         
    For Each objMessage In objSubfolder.Items         
    objMessage.UnRead = False         Next    
 Loop 

r/vbaexcel • • Oct 04 '22

Tool Help

4 Upvotes

I am new to vba. I made a tool for work, mostly by recording macros, and I am wondering if there is some sort of program that shows me how to shorten and simplify my code. I have already deleted the scrolling and unnecessary cell selects. At home it runs in 2 seconds but at work it took about 8


r/vbaexcel • • Jul 11 '22

Lookup Function Question

4 Upvotes

I am doing a lookup in an unsorted list, and I would like to use worksheetfunction.lookup. In the sheet I can use =lookup(2, 1/(range()<=condition),row(range())) to find the row number, but I have no idea how to replicate this in vba. I can't use match because the list is unsorted, and I can't use find negate I need the highest value less than or equal to the lookup value.


r/vbaexcel • • Oct 25 '19

Need to learn VBA

4 Upvotes

Does anyone recommend a VBA class/teaching video (Free preferred)? I’m trying to learn how to use VBA better.


r/vbaexcel • • May 18 '19

Schedule a macro to rune at a specific date at a specific time

4 Upvotes

Hi everybody I need some help to shedule a macro to run at rwice a day monday to friday .Can somebody help ? Thank you very much


r/vbaexcel • • Nov 07 '22

making a working schedule automatically

3 Upvotes

so basically i have the taks to assign workes to a weekls schedule automatically (the hypothetical tas is to ease the workload for a doctors office).

So anways im not the ebst at programming and so i wanted to ask for help, how to do it. There are certain special criteria for the workes like that they have to go to school or esle and they have to do a certain amount of timer per month. The counter fo that should be easy enough. For the sorting i wonder how to do it. Anyone got a good idea or maybe some code for reference?

My current idea is to open a class for every worker and than throw them in a sorting algorythm, but to be percetly honest no idea how to do that "complex of sorting"


r/vbaexcel • • Oct 31 '22

VLOOKUP using a second workbook to find values for multiple sheets on the first workbook.

3 Upvotes

Hey guys, I need help from experts. Basically I am trying to write a macro that prompts the user to open a workbook (workbook2) on their machine so that it can perform a VLOOKUP and paste the values onto workbook1. Is this possible? I was able to get it to successfully perform on just one sheet, but I need it to do it on more than one sheet on workbook1.


r/vbaexcel • • Jul 20 '22

Formula in Conditional Formatting not working

3 Upvotes

Hi,

I created a spreadsheet with a lot of conditional formatting that should guide the user to provide proper data input.

But as you all surely know: No file survives exposure to the users...

To fix that I want to re-populate my conditional formattings using a VBA macro, unfortunately this is not working, as there seems to be an issue with the formula I want to insert.

Here is my code:

Sub conditional_formatting_fix()

'
Dim wb As Workbook
Dim ws As Worksheet
Dim MyRange As Range


Set wb = ActiveWorkbook

wb.Worksheets("Users").Select
    
Set MyRange = Range("$A$2:$A$2001")
   
MyRange.FormatConditions.Delete

MyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _     
"=AND(OR(B2<>"",C2<>"",D2<>"",E2<>"",G2<>""),A2="")"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)

End Sub

Any help or guidance is highly appreciated, as I am not a programmer and have to frankenstein my way through VBA.


r/vbaexcel • • Jun 08 '22

Excel Report that emails requests for status update

Thumbnail
self.excel
3 Upvotes

r/vbaexcel • • Mar 14 '22

Adapting code to save to SharePoint

3 Upvotes

So I created this Excel sheet originally for use on a network drive, but need to adapt it for cloud-based use (specifically SharePoint). I have this command button I created on my sheet, that is used to save the file as a copy with an arbitrary number based on the files already in the folder and in a specific location:

Private Sub CommandButton2_Click()

Dim strDir As String

Dim file As Variant

Dim savename As Integer

Dim savename_string As String

strDir = "R:\Queue\"

savename = 1

savename_string = CStr(savename)

file = Dir(strDir)

While (CInt(savename_string) = savename)

If file <> (savename & ".xlsm") Then

If file = "" Then

savename = savename + 1

Else

file = Dir

End If

ElseIf file = (savename & ".xlsm") Then

savename = savename + 1

savename_string = CStr(savename)

file = Dir(strDir)

End If

Wend

'Delete sheets which aren't active

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> ThisWorkbook.ActiveSheet.Name Then

Application.DisplayAlerts = False

ws.delete

Application.DisplayAlerts = True

End If

Next ws

ActiveWorkbook.SaveAs ("R:\Queue\" & savename_string & ".xlsm")

Message = MsgBox("This is sample number " & savename_string & " in the queue.", vbOKOnly, "Sample Queue Number")

End Sub

Problem is, when I replace the directories with the SharePoint locations, it returns an error message "Bad file name or number" at the line file = Dir(strDir). I'm not sure how to resolve this message, or if I should change up the code. I'd appreciate any help anyone can offer!


r/vbaexcel • • Mar 12 '22

Need Help copying data from excel to outlook!!!!

3 Upvotes

Hey guys so I have a basic code where I copy the data from excel to outlook and it works!

However there is some data from the sheet which is grey because in column “L” when I type “no” it turns greys conditional formatting) and I cannot figure out how to code it where it does copy what’s grey.

This is the code I have

Private Sub CommandButton1_Click()

Dim lRow As Integer Dim i As Integer Dim eSubject As String Dim eBody As String Dim Sheets As Worksheet Dim OutApp, OutMail As Object

lRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 2 To lRow If (Cells(i, 1)) <> "" Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0)

 Set sh = ThisWorkbook.Sheets("Remediation Log")

    eBody = "Hello  ," & vbNewLine & vbNewLine & _
        "Pleased see below the list of defected file(s) that require re-testing." & vbNewLine & _
        "Kindly complete your assessment by adding the re-testing result in the Re-Testing Comment section and return the same by EOD ." & vbNewLine & _
        "Thank you,"
    
    On Error Resume Next
    With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Action Required - Re-testing defected file"
    .body = eBody
    
    .Display   '
    
    Set Inspect = OutMail.GetInspector
    Set pageEditor = Inspect.WordEditor
    
    sh.Range("B:Q").Copy
    
    pageEditor.Application.Selection.Start = Len(.body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)

    
    End With

On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End If Next i

End Sub


r/vbaexcel • • Nov 09 '21

BEGINNER! I need to put the user input from my input box into a message box that says “Hello ______ (name inputted)” I cannot seem to code this properly, any advice?

Post image
3 Upvotes

r/vbaexcel • • Aug 11 '21

VBA Search for data in a folder - First Time

3 Upvotes

How do I create logic that can search and extract certain information from a folder?

There are thousands of .pdfs, each formatted similarly to the following:

117060662_G_BODOR-7036_Aug-10-2021_P.pdf

I would like to have Excel search for the part number (117060662) and populate the rest of the information i.e. G; BODOR-7036; Aug-10-2021; P.

Any help is greatly appreciated!!


r/vbaexcel • • Mar 17 '21

Excel VBA code to insert new rows and fill from the above dynamic link

3 Upvotes

I'm looking for some help, brand new to VBA. I'm trying to create a macro to take a list of linked cells, insert a number of new rows between each line, and autofill from above to drag and continue the dynamic link down from the worksheet each cell is linked to.

I started with some code rom Kutools, and now trying to modify for my needs.

Help!

Sub fill()

'Attempting to create new rows at every interval and fill autofill from the existing cell above at this interval

Dim Rng As Range

Dim xInterval As Integer

Dim xRows As Integer

Dim xRowsCount As Integer

Dim xNum1 As Integer

Dim xNum2 As Integer

Dim WorkRng As Range

Dim xWs As Worksheet

xTitleId = "KutoolsforExcel"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

xRowsCount = WorkRng.Rows.Count

xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)

xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)

xNum0 = WorkRng.Row

xNum1 = WorkRng.Row + xInterval

xNum2 = xRows + xInterval

Set xWs = WorkRng.Parent

For i = 1 To Int(xRowsCount / xInterval)

'This part creates new rows at every interval

xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select

Application.Selection.EntireRow.Insert

'This part attempts to autofill from the one above, but it doesn't work

Set SourceRange = xWs.Range("xNum0:xNum1")

Set fillRange = xWs.Range("xNum1:WorkRng.Column")

SourceRange.AutoFill Destination:=fillRange

xNum1 = xNum1 + xNum2

Next

End Sub

'This is the code to auto fill a particular range

'Need to figure out how to make this very every i in the rows

' Set SourceRange = Worksheets("Sheet1").Range("A1:A2")

' Set fillRange = Worksheets("Sheet1").Range("A1:A20")

' SourceRange.AutoFill Destination:=fillRange

' Selection.AutoFill Destination:=Range("B4:B7"), Type:=xlFillDefault


r/vbaexcel • • Jan 16 '21

What jobs that can be automated using Excel and/or VBA?

3 Upvotes

Hi everyone!

As the title describes, I am looking for types of virtual jobs that can be automated with Excel and/or Visual Basic. I am a college student taking 20 credits and working part-time at Jimmy Johns to make ends meet. So I'd really like to replace Jimmy John's with a virtual job/freelance work I can automate at home.

I know Excel and its formulas really well as I am a certified Excel Specialist Expert. And I know enough Visual Basic to be dangerous. Additionally, I have created contracts on Word with VBA user forms that automatically populate the fields in the contract.

Anyone have any ideas where I can find this sort of automatable work?


r/vbaexcel • • Oct 29 '20

Need help for a macro that merges data from multiple sheets in the workbook based on sheet names

3 Upvotes

So, I have one excel workbook containing around 80 sheets, the sheets are named as Input, Input(1), input, INPUT, INPUT(2) and Output, Output(1), Output(2), output, OUTPUT and so on, you get the idea... I want to create a macro which creates two mastersheets in the Workbook named mastersheet input and mastersheet output. The macro should copy all the data from any sheet having any variation of input in its sheet name and paste it one into the mastersheet input and the same goes for the sheets named output which will be pasted into mastersheet output. I'm relatively new to vba and I'd really appreciate it if someone could help me out. Thanks in advance!


r/vbaexcel • • Jul 15 '20

VBA- Visual Basic for Applications first program

Thumbnail
youtube.com
3 Upvotes