VBA

10K

Solutions

3K

Contributors

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello All.  First let me start by saying "TRIM" DOES NOT Work.

Using Excel VBA, I used the InStr Function to separate the ID Numbers using the comma.
The problem is, this data is in an Excel Cell.  The guy used the Alt + Enter to move to the next line after adding the comma.
When I pull it into a variable, I get one of two results.
" Paragragh 3.5.3"  or "Paragragh 3.5.3 "
It has a blank space before or after each one.  I am not sure it if is a Carriage Return or not.
I have not been able to get rid of it.  I need that term to use a search.  Because of the extra spaces, they are not being found even it they are there.

Fake Data:

PUNISHER1.2,3,
CYCLOPS,
VISION,
HULK,
GHOST RIDER,
THOR

  strLeftToCaptureCheck = Left(strToCapture, 1)
  If strLeftToCaptureCheck = "" Then
            MsgBox "Check"
  End If

As a test I used the code above to see if there was something there.
The result was strLeftToCaptureCheck  = "".  The "" is not a blank space.  It skipped over the message box.

Is there a way to find the first character position or just get rid of whatever those blank space are?
I never know if they are going to show in the beginning or the end.  I need a way to look at both ends.

Thanks
Nate
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

I would like be able to get same output as this workbook but without any data in column 'D
29118917_to_add_d_column.xlsm
0
In column A are names and in column B I have emails. I then removed all of the emails that are present in my unsubscribe list. Now I have about 1K less emails. I want to now match the name with the final list of emails. Anyway to do that using an Excel formula or Macro?
1
select  0 and 1

as show  

 gg22222.png
then

gg11111.PNG
then

gg23333.png
the total
gg11111.PNG
2N_01_to_count.xlsx
0
Hi Experts,

Please help me with code. I have attached excel file. In that Column D has resource name, there if there name is repeating for more than 2 times shows some pop up or highlight the repeated.
Book1.xlsx
0
eliminated the follows after the key  number
29118287d--1-_to_add_after_key_clea.xlsm
0
Following code is for deleting one sheet on the workbook. I need to delete multiple sheets from one workbook. Can anyone amend below code tol delete multiple sheets(with sheet name).

Public Function WorkSheetDelete_YE_FocusSites()
    On Error GoTo WorkSheetDeleteErr

    Dim mySheet As Object
    Dim xlApp As Object
    Dim wrkb As Object
    Dim strName As String

    strName = "C:\kk\kkk.xlsx"
    Set xlApp = CreateObject("Excel.Application")
    Set wrkb = xlApp.Workbooks.Open(strName)
    xlApp.DisplayAlerts = False
    For Each sh In wrkb.Worksheets
        If sh.Name = "k1" Then
        wrkb.Worksheets(sh.Name).Select
        xlApp.ActiveSheet.Delete
        End If
    Next
    wrkb.Save
    wrkb.Close
    Set wrkb = Nothing
    Set xlApp = Nothing

WorkSheetDeleteErrExit:
    Exit Function

WorkSheetDeleteErr:
    MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
    Resume WorkSheetDeleteErrExit
End Function

Thank you
0
key number is 44
then when it repeat  after the row 48

need to delete the follows numbers
a1.PNG
with an button
Eliminated_repeat_data.xlsm
0
I need to read  a number of comma delimited csv file from a specific folder and put each file into a worksheet with the name of the worksheet being the name of the csv file.  How do I do this using Excel VBA?
0
I'm using the below code to delete outlook items wit xlsx attachments. The issue encountered is that only some of the emails are deleted. Any thoughts

On Error GoTo SaveAttachmentsToFolder_err

    Dim ns As NameSpace
    Dim Inbox As Outlook.MAPIFolder
    Dim Item As Object
    Dim Atmt As Outlook.Attachment
    Dim FileName As String
    
    Dim SubFolder As MAPIFolder
    
    
    
    
    Set ns = GetNamespace("MAPI")

    Set recip = ns.CreateRecipient("OMHA.HQ.MI.REPORTS")
   
    Set Inbox = ns.GetSharedDefaultFolder(recip, olFolderInbox)
    
    Set SubFolder = Inbox.Folders("MATS")


    If SubFolder.Items.Count = 0 Then
    
        MsgBox "There are no messages with attachments in the MATS folder.", vbInformation, _
                "Nothing Found"
        Exit Sub
    End If

    
    
    
    'Delete Outlook items
    For Each Item In SubFolder.Items
    For Each Atmt In Item.attachments
    If Atmt.Type = 1 And InStr(Atmt, "xlsx") > 0 Then
    Item.Delete
    End If
    Next Atmt
    Next Item
   
    
    
  
    
    ' Clear memory
SaveAttachmentsToFolder_exit:
    Set Atmt = Nothing
    Set Item = Nothing
    Set ns = Nothing
    Exit Sub
' Handle Errors
SaveAttachmentsToFolder_err:
    MsgBox "An unexpected error has occurred." _
        & vbCrLf & "Please note and report the following information." _
        & vbCrLf & "Macro Name: GetAttachments" _
        & vbCrLf & "Error Number: " & Err.Number _
        & vbCrLf & "Error Description: " & 

Open in new window

1
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Column A & B can contain over 500,000 values and changes over time.

What is the fastest code that will find all cells in column A with "Hello World" and put the corresponding value(s) from column B in an array without iterating through all the rows.

Example:
A.                      B
144.                  ddgh
24477.             355:+fd
Hello World     aaaa
1188.               &554&
ffhj
Hello.               xxccffee
Hello World     bbbb

In the above example it would put aaaa and bbbb in an array because "Hello World" is in column A for them both.
0
Hello, need to add a Date/Time in the following statement:

CurrentDb.Execute "Insert into UserActivityLog (UserName, Activity, ComputerName) Values ('" & TempVars("UserName").Value & "','" & Activity & "','" & Form_frmLogin.CompName & "')"


to:  I added the field:  TimeStamp (data type is Date/Time but don't can't get the Values part right....

CurrentDb.Execute "Insert into UserActivityLog (TimeStamp, UserName, Activity, ComputerName) Values ('" & TempVars("UserName").Value & "','" & Activity & "','" & Form_frmLogin.CompName & "')"
0
tally_duration.xlsxtally_duration.xlsxI have a raw data set that includes hourly pH values at multiple sites ("Sonde_Names") for two three-month periods: one in year 2013 and another in year 2014.

For each of the "Sonde_Names", I need to generate a list showing the number and duration of events where the pH is below a value of 5.5 (the field "BlwThresh" indicates which records are below 5.5). An 'event' is defined as one or more contiguous values of 1 in the "BlwThresh" field. This list also needs to include the associated values for "Sonde_Names", "Year", "Month", "Day", and "Hour", in which the event began.

(If possible, the "Event_num" field should reset to a value of 1 when starting to process each unique set of "Sonde_Names" and "Year" values.)

The worksheet named "tally_duration" shows a few manual examples of the objective.

Many thanks in advance for a (hopefully portable) automated solution.
0
Hello experts,

In order to properly manage some of the procedures reported in my personal.xlsb file I am looking for a procedure part related to msgbox vbyes, vbno.
The idea is to prior to run the procedure I would like to display a msgbox which says: "Are you sure that all the requirements related to this procedure have been properly set up such as: column format, cells values etc..? If so click on Yes to continue else click on No.

Something like this:

Sub MessageBoxExample()
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Ask Your Question Here, OK?"
 
    ' Dialog's Title
    strTitle = "My Tite"
 
    'Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "NO!"
    Else
        MsgBox "Yes!"
    End If
End Sub

Open in new window


What is the best practice to set up msgbox related to vbyes and vbno questions-answers?

Thank you very much for your help.
0
Hello Experts,

I have the following procedure which allows me to rename folders:
Sub Rename_Folders()
Const FILEPATH As String = "C:\"
Dim strfile As String
Dim filenum As String
strfile = Dir(FILEPATH)
Dim lr As Long
Dim ws As Worksheet
Dim old_name, new_name As String
Set ws = ActiveSheet
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 2 To lr
''strOldDirName = FILEPATH & Sheets(1).Cells(i, 1).Value
''strNewDirName = FILEPATH & Sheets(1).Cells(i, 3).Value
strOldDirName = ws.Cells(i, "A").Value
strNewDirName = ws.Cells(i, "B").Value
Name strOldDirName As strNewDirName
Next i
End Sub

Open in new window


I would like to properly manage errors such as:
1-If folder reported in column A doesn’t exist skip the cell, display msbgox “Folder cannot renamed as it doesn’t exist” and continue with the next cell
2-If target folder reported in column B cannot be renamed, display msgbox “Folder cannot be renamed”

Please find attached example file.

If you have questions, please contact me.
Rename-folders-based-on-columns.xlsm
0
when i hit 00 to get the results in sheet 2

is not working properly and need eliminated the arrows too

x33333.PNG2n_fix_00_.xlsm
0
Vba code to send sms from excel  by api
I will send msg to only 1 number that is my number and my number will be fixed it will not change
let my number be  1234567891
and api is xyz
And message that i have to send to my mobile is ALERT

I have to do this by vba only
In my case my mobile number is fixed   so i don't  need to enter my number manually
the code will run and sms is sent
0
I'm using the code below to download Excel attachments from a Outlook Mailbox's Inbox  to a folder on my drive. The download works good, however I would like to download from a folder (MATS) other then the Inbox. Any thoughts?


 
  Dim ns As NameSpace
    Dim Inbox As Outlook.MAPIFolder
    Dim Item As Object
    Dim Atmt As Outlook.Attachment
    Dim FileName As String
    

    
    Set ns = GetNamespace("MAPI")

    Set recip = ns.CreateRecipient("OMHA.HQ.MI.REPORTS")
   
    Set Inbox = ns.GetSharedDefaultFolder(recip, olFolderInbox)



    If Inbox.Items.Count = 0 Then
        MsgBox "There are no messages in the Inbox.", vbInformation, _
                "Nothing Found"
        Exit Sub
    End If

    For Each Item In Inbox.Items
        For Each Atmt In Item.Attachments
            If Atmt.Type = 1 And InStr(Atmt, "xlsx") > 0 Then
                FileName = "C:\attachments\" & Atmt.FileName
                Atmt.SaveAsFile FileName
               
            End If
        Next Atmt
    Next Item

Open in new window

0
Hi,

I'm trying to loop through the sheets of the workbook. So the logic is if the sheets name matches Dino List's range("E" & i). value then it copies whole range and pastes it onto the matching named sheet. But I'm stuck with the for loop on line 'Next i'. It says 'next without for'. Don't know where I'm wrong?

Sub dinosaur()

Dim i, j As Integer
Dim diet As String
Dim ws As Worksheet
Dim lastrow As Long

 lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
 

Sheets("Dino List").Activate

For Each ws In ActiveWorkbook.Worksheets
   For i = 2 To lastrow
        diet = Sheets(1).Range("E" & i).Value
        If ws.Name = diet Then
        Sheets(1).Range("A:K").Copy
        Worksheets(diet).Activate
        ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
        ActiveSheet.Paste
   Next i
Else: Next ws
End If
End Sub

Open in new window

0
Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

To remove duplicate line if this line is based on a repeated route and also add 'Y' in column F to indicate that the next line was removed.

One can filter column b, c and d to be all equal to 0 and will see all the same route appear in column A.

See attached for detailed examples and comments.

And of course if there are more than 1 duplicates meaning like this:

108.004.600 - 108.004.600      0      0      0      84.75291788  
108.004.600 - 108.004.600      0      0      0      1.968908303  
108.004.600 - 108.004.600      0      0      0      2.417233924  
108.004.600 - 108.004.600      0      0      0      1.713124927  

then the result should be like this (3 lines below are removed) :
108.004.600 - 108.004.600      0      0      0      84.75291788   Y

and column G states how many lines were removed.

if there is no duplicate, for example:
405.091.200 - 405.091.200      0      0      0      6.337535366

then do nothing for this line.

Same concept applies to all.




Thanks !
Row-Indicator-Remove-Duplicate-EE.xlsx
0
Hello
I need your help please in the attached sample file ( original file with data over 100,000 rows)
Column A has many duplicate values and in column S the value is corresponding to this value but its enter only in one cell.
What I want is to copy the value mentioned in column S in front of all corresponding value in column A.
 I hope this clear
LIST-1.xlsx
0
1- find buttons should be in  OUTPUT sheet
find 2 find 3 find 4

2- SELECTED show results

x111.PNG
x222222.PNG29118087--1-_to_find234_to.xlsm
0
Using Excel keyboard shortcuts

ctrl + semicolon yields the DATE
ctrl + shift + semicolon yields the TIME

both non-dynamic

Need a single keyboard shortcut that will insert

DATE TIME

Both, also non-dynamic

Thanks!

OT
0
select data as show

tehn show in SELECT sheet

rrr1111.PNG29117915_to_make_selected_3n_to_4N.xlsm
0
I am trying to write vba code in MS Access 2016 to close a subform within a navigation form
0

VBA

10K

Solutions

3K

Contributors

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.