Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

VBA

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, I would like to make some edits to my code to make it work with different types of worksheets. Currently I'm copying and pasting fixed cells, but I would like to incorporate search/match functions in case the information i need are in different cells on different worksheets.

Worksheet Information:
- B2 to B5: Titles of department info
- C2 to C5: Corresponding department info
- Row 8: Header for products
- Cell A8 = serial number. Cell A9 = "Product Information"
- If the department's worksheet has no products, Cell A9 would say "NIL"
- However, some departments might have different formats (eg extra blank column or extra row which cause the code to copy the values

Code's current function:
1. Add 4 columns on left
2. Copy and paste C2 to C5 (now G2 to G5)
3. Copy rows and paste on another sheet (from row 9 to last row)

     
        'Loop each worksheet
            Dim wscount As Integer
    Dim a As Integer
    wscount = ActiveWorkbook.Worksheets.Count
    For a = 1 To wscount
        'activate worksheet (a)
        Worksheets(a).Activate
        'skip if NIL
        
        'Add column and paste department info
        Dim i As Integer
    
Columns("A:D").Insert shift:=xlToRight, _
      CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow

    
    'Loop for cells with content
i = 9
    Do While Cells(i, 6) <> "" 'Currently this includes "NIL"
        Range("G2").Copy
        Cells(i, 1).PasteSpecial Paste:=xlPasteValues
       

Open in new window

0
Free Tool: ZipGrep
LVL 11
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

looking for V_OUT sheet

for example im looking for 65 it appear 8 times

but when hit find 2 it only show 3 times
65434677665.xlsm
0
In sheet 1 Copy the data if column A contains True and if false then leave that And paste the data in sheet2
Kindly check my workbook in sheet1 i have highlighted the data to copy with green colour and in sheet2 i have highlighted the pasted data
Example.xlsx
0
have this solution need can button to clean the HIST_DTA
29078876--2-23421.xlsm
0
need the percent as show here
needed

222222222222222222222222222222222222.PNG29078870aaaaa.xlsm
0
need create an history to be show  in HIST_DTA






of the selected  i mean to compare ( what  need to be selected )

888888888888888888888888888888888888.PNG29078859b.xlsm
0
1-need the find numbers be show as the result is  show  in A4   V_OUT sheet

2-need show the total columns od data in b1  V_OUT


666666666666666666666666666666666666.PNG8598744458.xlsm
0
Hi all.

I have an Access form with a combobox whose row source is set to "Table/View/StoredProc" and is populated in the On Enter event. There are 2 columns with widths (0";1") the first column is the ID and the second is the name, the end user sees the name but when they save the record the ID is what gets saved in the table.

The problem we're having is if the end user enters the combobox via the mouse or tab without clicking on the down arrow it temporarily changes the selected value/text of the combobox to the first entry of the combobox but then it reverts back to the original selected value/text when you leave the combobox.

Any idea how we can stop this from happening?

Thank you in advance.
0
MS Access How To Control and Open a POS Cash Register Cash Drawer With VBA
0
I want to be able to enter details on an Access form, which will form the basis of an Outlook calendar appointment. Not only in my calendar but also another team calendar, which i wanted to hard code. I have done the code below:

Private Sub Command127_Click()
' Save record.
DoCmd.RunCommand acCmdSaveRecord

'add to Outlook.
Dim olApp As Object ' Outlook.Application
Dim olappt As Object ' olAppointmentItem
Set olApp = CreateObject("outlook.application")
Set olappt = olApp.CreateItem(olAppointmentItem)

Set olappt = olApp.CreateItem(1)
With olappt
.Start = Me!ContractStartDate & " " & Me!ContractStartTime1
.End = Me!ContractStartDate & " " & Me!ContractStartTime2
.Subject = Me!CaseTitle
.location = "Contract Start Date"
.Body = ""
.reminderminutesbeforestart = 15
.reminderset = True
.MeetingStatus = olMeeting
.recipients.Add ("123@abc.co.uk")
.recipients.resolveall
.Save
End With
Set olApp = Nothing
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Appointment Added!"
End Sub

However, it shows in my calendar fine, I check the appointment, the team calendar name has been resolved fine, but it doesn't appear in there calendar. Is there some code I can add to achieve this?

Any help would be greatly appreciated.

Thanks
Gareth
0
Receive 1:1 tech help
LVL 11
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

how should i setup or design the VBA code wit the condition as below ?

I would need all new incoming emails (doesn't bother who is the sender) , if detected keyword (i got 5000 server name) in email body, auto categories as x(color) or y (color).
Should the code need to pointing to Excel/txt file or all 5000 keyword should import into the code ?
hmmmm....


\\FYI, the 5000 keywords is unique.
0
I want to do something very easy.
I just want to place some code in a string and execute it.
Tried Eval, but no luck.

Example:

Dim strVal As String
strVal = "Forms!frm_000_010_REFERENCE_MAIN.txtpending.visible=true"
Eval (strVal)
0
Sheet1 is a linked worksheet, so every cell contains a formula.

Sheet2 is a validation spreadsheet to ensure that all minutes entered in Sheet1, Column E have an associated date entry in Sheet1, Column D. To that end, I'm trying to write a validation formula that meets this criteria:

If Sheet 1, Column E has a number >= to 1, and Sheet1, Column D does not have a date, then populate Sheet2 with "Missing", otherwise "".

=IF(AND(Sheet1!$D2=0,Sheet1!$E2>=1),"Missing","")

The above formula is not working, as despite Sheet1, Column D missing dates in cells D7:D11, Sheet2 does not indicate this by populating B7:B11 with the word "Missing".

I have included a spreadsheet to demonstrate the issue.

Thanks,
Andrea
EE_validation.xlsx
0
I had this question after viewing How to add a parameter to this custom function for the number of columns?.

Function CombineText(rg As Range) As String
    Dim col As Integer
    If rg.Rows.Count = 1 Then
        For col = 1 To rg.Columns.Count
            If rg.Cells(1, col) = "" Then
                CombineText = CombineText & "00"
            Else
                CombineText = CombineText & Left(rg.Cells(1, col), 2)
            End If
            If col < rg.Columns.Count Then
                CombineText = CombineText & "-"
            End If
        Next col
    End If
End Function

In this function, the first three characters of cells are in the format "01-", "02-", etc. is picked up from cells from a number of columns.  I would now like to convert the "01-" to A, "02-" to B, etc.  There are no numbers above 26.  If a cell is blank, I would like its value to "X".
0
I have VBA code that grabs a customer name and assigns it to a variable called Cus. How do you display the variable on an Access report?
0
I have a UserForm in PowerPoint with a button on it that opens the Excel file picker dialog. When the file is selected, PowerPoint VBA then opens the selected Excel file in a hidden instance, loads some data from it into the UserForm and leaves the file open whilst the UserForm remains loaded/visible. When this happens, the PowerPoint icon in the Windows taskbar pulses and a second PowerPoint icon hops up and down, indicating that PowerPoint no longer has focus. This is confirmed by the modal UserForm remaining in a defocussed state.

I added several variations of the AppActivate statement to the PowerPoint VBA code but none work, although no error is raised e.g.

AppActivate Application.Caption
AppActivate "PowerPoint"

Open in new window


However, if I use the same AppActivate command(s) from the Excel VBE when in the defocused state above, the command works as expected.

How can I get the PowerPoint VBE to return focus to PowerPoint in this case?
0
have this solution but then  need to count the coulmns with data

also
when an number appear need be color green the cell

5555555.PNG456778654.xlsm
0
each column have  numbers

V_OUT sheet is where to work with
 

example  03  in each column  
 show the count by column in row 6 above the column
then the total count in b6

AAAA23123.PNG674398_out.xlsm
0
I had this question after viewing How to write a custom Excel 2016 function that gets first two characters of cell if numbers?.

The current custom function works with 3 columns.  I would like to be able to use a different number in other situations.
0
Upgrade your Question Security!
LVL 11
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Need a help trouble shooting my outlook vba code process (below)

Before I run this code I create a series of emails using Microsoft Mail merge

I run the process and it creates the emails (may as few as one email or hundreds of emails )

This is done with outlook enterprise offline

I the move the emails to a subfolder of the inbox and run the following code to add attachments and build in a delay if there are a lot of emails to send

At .send the macro stalls and give me an error message "Runtime error '-2147467259 (80004005): This method can't be used with an inline response mail item."

Sub a_Introduce_EPM_To_New_Clients()
    Dim mai As MailItem 'Object
    Dim acct As Long
    Dim Delay As Date, Start As Date, n As Integer
    Dim UpdtCount As Integer
    Dim FirstNm As String
    Dim AddAttachment As Integer
    Dim EmailsPerHour As Integer
    Dim FolderCheck As Integer, FolderName As String
    Dim EmailNo As Integer
    Dim ofldr As Object
    
    Dim AddAttachmentIntroEmail As Integer
        
    Dim recips As Outlook.Recipients
    Dim recip As Outlook.Recipient
    Dim pa As Outlook.propertyAccessor
    Const PR_SMTP_ADDRESS As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
    
    EmailNo = 1
   
    MsgBox "Select Email Folder"
    Set ofldr = Session.PickFolder

    AddAttachment = MsgBox("Do You Want to Add Listed Project Attachments", vbYesNo + vbDefaultButton2)
    AddAttachmentIntroEmail = MsgBox("Is this an 

Open in new window

0
Hi,

I am looking for a VBA code which can be used in converting "Text to Column", I need to have dynamic "FieldInfo".

The code needs to work for "n" number of columns

Example:

1 FirstName LastName

Or

Address1 Address2 FirstName LastName

The code should work for both the examples
0
Hi all,

I used RGB value of (Red=180, Green=198, Blue=231) in text and it showing the same rgb color in color pallet also.

LONG value for this RGB is  15189684 but while i am using the code (Selection.Font.Color) to get the rgb value, it returns "-738132122" insted of 15189684.

Please any one guide me.
0
What VBA code would I need that writes "Call FunctionOrganizeData" in the "Private Sub Workbook_Open()" subroutine if "Call FunctionOrganizeData" does not exist there yet.
0
I have a lengthy Excel VBA script that works quite well. I have recently added to it the VBA code to send certain worksheets by email to selected individuals via Outlook from inside Excel VBA. Now, I want to add code that will call six separate one-page Word documents (already written and saved to disk as six .docx files) and print 1 copy of each of these six single-page Word document files at the end of the Excel VBA script, just before the script terminates and Excel closes. I've never called a Word document to print from within Excel with VBA, but, like with Outlook, I know it can be done.

Do any of the experts have a code snippet or sub that I could try within my Excel VBA script that would do this?

Thanks!

Glenn
0
Hi

I want to use a barcode reader with my VB.net or VBA software.
Where do I start?

Thanks
0

VBA

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.