We help IT Professionals succeed at work.

VBA

12K

Solutions

4K

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.

Hi

I want to automate the calculation of balance sheet numbers in one workbook from another workbook that is sent every month. If I do it manually I would look for account numbers or words in the source document and add these together to get the number in the destination document.

Here is an example of just one formula ='[Freight Props - TB - Feb 2020.XLSX]General Ledger Trial Balance'!$H$30+'[Freight Props - TB - Feb 2020.XLSX]General Ledger Trial Balance'!$H$31

I was thinking of putting the source file name in one cell of the destination file and then using this in a the above formula. Then when the new source file is received just updating this. So for instance could I put "Freight Props - TB - Feb 2020.XLSX" in cell A2 and use that cell reference in the formula above?

Thanks
0
Hello experts,

The following procedure allows me to insert date in active slide.

I would like to insert date + time like this 29/03/2020 14:22:26

Sub Insert_Date()
    Dim oSld As Slide
    Dim oSh As Shape
    On Error Resume Next ' add your own error handling
    Set oSld = ActiveWindow.View.Slide
    Set oTextRange = oSld.Shapes.AddTextbox(msoTextOrientationHorizontal, _
    515, 0, 250, 400).TextFrame.TextRange
        With oTextRange
            .Font.Color = RGB(255, 0, 0)
            .Text = "Update date: "
            .InsertDateTime ppDateTimeddmmyy, True
        End With
End Sub

Open in new window


Thank you in advance for your help.
0
Experts,

I need VBA code to shade cells purple.
The condition is:
If column E = "Company A" or "Company B" then shade the cells in Column  K purple

How can I do that?
The conditional formatting does not work in this file.  
I think there could be some corruption so I want to test the formatting through VBA

Thank you
0
I have a spreadsheet where I have multiple rows of different data where I would like to consolidate and summarize on a single line for each ID.  It would be great to have some sort of macro that can take the data from the source tab and generate the data in the output tab (see attached spreadsheet).
ee_help.xlsx
0
I am trying to compare two separate sheets in Excel.  The problem is, each row does not match to the adjacent row.  I want to select a row and compare it to another row displaying what is missing in in either sheet.
0
Fix Win10 VBA Excel 365 loop / variable logic for conditional format.
If the row is a Total row and either (current month and current %, prior month and prior %, current ytd and current ytd%)  are higher than standard, highlight entire row
In test example line 34 should highlight, but none other

 I tried a few things with the conditional formatting variables and this compiles, but something is not working.
previous versions have highlighted every total row and the below one does not highlight any.
lastrow appears working, logic for is it is a total line  appears working.
cr (current row is probably working

Sub ConFormat()

Dim LastRow As Double
Dim myCell As Range

Dim cr As Long
' sv - startvar  sp startperc
Dim sv As Range
Dim sp As Range
' Set sv = Range("F2")
' Set sp = Range("G2")
Set sv = Sheets("Chk").Range("$F$2")
Set sp = Sheets("Chk").Range("$G$2")
cr = ActiveCell.Row
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

For Each myCell In Range("C11:C" & LastRow)
    If Left(myCell.Value, 5) = "Total" Then
'   If OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & cr)>sp), AND(ABS(O & cr)>sv,ABS(P & cr)>sp)) Then
' If (Abs(F & cr) > sv And Abs(G & cr) > sp) Or (Abs(J & cr) > sv And Abs(K & cr) > sp) Or (Abs(O & cr) > sv And Abs(P & cr) > sp) Then
     
      myCell.EntireRow.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
 "=OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & …
0
Hi EE

I’ve got four digits which I want to put in order so that I can compare them with other sets of four digits to see if they are unique.  For example, 2384 and 3842 once put in order both give 2348 and, under my rationale, they’d be considered not unique.

Any ideas?
Many thanks.
Ben
0
After running a macro to query a database that changes daily, I get two worksheets with formatted reports that may randomly vary between 10 and 15 rows each day.  

I would like to copy the rows from Sheet1, and paste it below the last row of data on Sheet2, leaving a blank row between the two reports.  I would add this code at the end of the macro to generate the merged report.
Example-of-Merged-Reports.xlsx
0
Hello Experts,

I am looking for an excel VBA or add-in to build a decision tree to be use in MS office apps.

Example:

20200326_140714-screenshot.png
Could you please let me know your thoughts.

Thank you for your help.
0
Is it possible to Trim the first 4 characters & the second last from the Json string below, the idea here is to remain with pure json, so that if I fail to iterate it , then I can have it printed and posted to the table manually.(Characters not required are  ]    j  and 4c)
Below is the Ms Access VBA code that takes it to text file , but not fully cleaned up.

Text file Export VBA

n = FreeFile()
    Open "C:\Users\chris.hankwembo\Desktop\Leader\test.txt" For Output As #n
    Print #n, strData
    Close #n

Open in new window

Json String

 ]    j{
   "PosVendor": "Nector Prime Accounting Solutions",
   "PosSoftwareVersion": "2.0.0.1",
   "PosModel": "Cap-2017",
   "PosSerialNumber": "100100001829",
   "IssueTime": "20200326115321",
   "TransactionType": 0,
   "PaymentMode": 0,
   "SaleType": 0,
   "LocalPurchaseOrder": "",
   "Cashier": "Admin Manager",
   "BuyerTPIN": "",
   "BuyerName": "",
   "BuyerTaxAccountName": "",
   "BuyerAddress": "",
   "BuyerTel": "",
   "OriginalInvoiceCode": "",
   "OriginalInvoiceNumber": "",
   "Memo": "",
   "Items": [
      {
         "ItemId": 1,
         "Description": "Cleaning Materials",
         "BarCode": 19,
         "Quantity": 1,
         "UnitPrice": 56,
         "Discount": 0,
         "TaxLabels": [
            "A"
         ],
         "TotalAmount": 64.96,
         "IsTaxInclusive": true,
         "RRP": 0
      }
   ]
}4c

Open in new window

0
Hi,
I have 2 textboxes in main form.

what i want is:

Every time i write in textbox 1, I want textbox 2 to get a NULL value and vice versa.
The action I did is via the IF command, only the problem is that textbox 2 is getting the value NULL, but it doesnt displayed in the form (not refreshed).
How can I refresh textbox 2 without saving to form.
0
display a prior month value in a query for a field.
In  this  Table:   dbo_QCS_CRS_SCORE
Example data:
JANUARY =  33.4
FEBRUARY =  98.2
MARCH  =    96.3


I have the fields JANUARY TO DECEMBER AS FIELD NAMES. IN "dbo_QCS_CRS_SCORE"
i WOULD LIKE TO ADD THIS TO THE QUERY BELOW AND SHOW THE  PRIOR MONTH SCORE.

so say this is March I need to display February field.
When April 1st is here. I need to display March field ?


SELECT [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME] AS NAME, QCS_CRS_EMPLOYEE_DIRECTORY.user_id, QCS_CRS_EMPLOYEE_DIRECTORY.EMPLOYEE_STATUS, dbo_QCS_CRS_SCORE.YTD_AVG
FROM QCS_CRS_EMPLOYEE_DIRECTORY INNER JOIN dbo_QCS_CRS_SCORE ON QCS_CRS_EMPLOYEE_DIRECTORY.user_id = dbo_QCS_CRS_SCORE.RACFID
WHERE (((QCS_CRS_EMPLOYEE_DIRECTORY.TITLE_DESCRIPTION) In ('On-Site Specialist','Desktop Specialist','Reference Analyst')))
ORDER BY [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME];

Open in new window



Thanks
fordraiders
0
I have a table called:
Rebate_Qualifier
3 fields:
Rebate_ID      Entity_Value      Qualifier_ID

"Entity_Value" will always have a value in the field.

I have 2 variables    rb   and   ent

rb = SLS-000118-MAIN-A1-5-R1
ent = 5

The Rebate_Id is null
Qualifier_ID is null

Table example below:
Rebate_ID	Entity_Value	Qualifier_ID
	                AABAA	
	                AABAA	
	                AABAA	
	                AABAA	

Open in new window


What I need:
I need to update the Rebate_Id field with  variable  rb
I need to update the Qualifier_ID field with  variable  ent


Thanks
fordraiders
0
I have a worksheet that runs a macro to copy the data from one sheet ("WAV_Activity_Recap") to another ("Impact") if the values in column E + F do not exist in the WAV_Activity_Recap sheet. All works fine. My issue is I only want to copy the highlighted columns on the WAV_Activity_Recap worksheet to the Impact sheet (I do not need the extra columns on the Impact sheet).

Again, the macro works fine for copying if all the columns counts are the same.

Suggestions?
ImpactXfer.xlsm
0
Excel 365 Windows 10 VBA Conditional formatting
PLTest.xlsx is small sample with data values changed
Corporate monthly #s  tab usually < 1000 rows
place 10000 in F2 10% in G2
format each qualifying row yellow from row2 to last row
Part 1  Is it a total line?     If Left(c2,5) = "TOTAL"  
Part 2 Is it an account line?  isnumber(b2) Account
Macro would run with these default numbers
I would like for the user to be able to change numbers and if necessary rerun the macro

Sub CF_Var()
'
' Macro1 Macro
'
    Dim Lastrow As String
   Lastrow = CStr(wksht.Cells(2, "C").End(xlDown).Row)
   
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "10000"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "0.1"
    Range("G2").Select
    Selection.NumberFormat = "0.00%"
    With Selection.Font
        .Name = "Century Gothic"
        .FontStyle = "Bold"
        .Size = 14
    End With
    Range("F2").Select
    With Selection.Font
        .Name = "Century Gothic"
        .FontStyle = "Bold"
        .Size = 14
     End With
     'IF left(c27,5) = "Total") start with c2 and check to last row.
'NEED LOGIC HERE to check all rows for TOTAL  
'Version TWO check if column B is a number
  Rows("12:12").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR(AND(ABS($F27)>$F$2,ABS($G27)>$G$2),AND(ABS($J27)>$F$2,ABS($K27)>$G$2),AND(ABS($O27)>$F$2,ABS($P27)>$G$2))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    

Open in new window

0
Hi,

Would like to know how to check whether an Excel workbook is currently opened or not by using Excel VBA?  I'm now using Office 2013.  Thanks

Cheers
Stanley
0
adding a new number sequence to an existing alphanumeric sequence.

I have a dropdown with alphanumeric sequence that either ends in  R1 OR R2 OR R3  OR R4  etc..

and/or  also    I1  , I2 , I3 , I4 etc...

SLS-00612-MAIN-I1
or
SLS-0022-MAIN-R2

What I need:

When i press to save a record i need to automatically come up with the next last 2 alphacharacters in the sequence.

i.e.
So i make a choice from my combo box and the selection is
SLS-023-MAINROOM-R2

The new number is going to be :
SLS-023-MAINROOM-R3

i.e.
So i make a choice from my combo box and the selection is
SLS-006627-MAIN-I1

The new number is going to be :
SLS-006627-MAIN-I2

Thanks
fordraiders
0
Experts,

I am trying to combine the below in an unbound field on a report.
Maybe its better to do it by VBA?  
Not sure which way is easier.

thank you...

In English:
If tblLetterOfCredit.PaidInFullYN = True then "Paid In Full", if not then Dsum but only if tblPayments.PaymentDate is not null (meaning there is a date entered)

--------------------------------------
If tblLetterOfCredit.PaidInFullYN = True then "Paid in Full"
Else:

If is not null [PaymentDate] then Dsum [Amount].  How to add Is Not Nulll to below?
=iif(DLookUp("[PaymentDate]","tblPayments","[LCID] = " & [LetterOfCreditID]
dsum("[Amount]","tblPayments","[LCID] = " & [LetterOfCreditID]
0
I have a series of sets of Excel files. In each set, I have a group of Excel files all of the same format (same number of columns). All are single sheet outputs. I need to combine these into a single sheet excel file.  

I need to add in the name of each file in a new column after column 1, on each line. This way I know the source of the data.

I will have a directory in which are all of the source files (no other files)of one set. I want to place in this directory a NEW excel file containing the VBA that will combine these files into the NEW excel file.  

I have attached four source Excel files and a copy of the solution Excel file. I use excel 365 on Windows `10DonOHara.zip and Windows 7.

Thank you,
Don OHara
0
I have an Excel Add-In that has a macro which can be started using a shortcut key.  I have added another module in the add-in, copied the existing macro, and modified it to perform a different function.  How do I add a shortcut key to this new macro that is different from the one used for the original macro.
0
Hi Guys
In order to update my website I need to replace 3 access tables: table1, table2 and table3.
odbc for the sql server is 'PKC'
I am looking for vba code which will
1. delete the above 3 tables
2. replace them with 3 fresh tables (same tablenames)
The vba code is to run on access module  (I am using office 365 with ms access)
Any help is appreciated
Thanks
Dory
0
Hello experts,

Following the question: Question 29176080
which allows me to keep and remove text based on character. I need some help to add to my add-in to be able to swiftly use the procedure on a daily basis.

-Reference procedure to be added in the add-in located at: EE29176080.xlsm
-Reference add-in LD-add-in.xlsm

It should be added at: Keep_Remove_Text_Based_On_Char
20200320_095450.png
Located at:
20200320_095616.png
If you have questions, please contact me.

Thank you in advance for your help.
LD-add-in.xlsm
EE29176080.xlsm
0
Hello experts,

The following attached file contains a reference userform which allows me to add numbers or letters on columns.
20200319_155152-screenshot.pngRef EE comment available at: #a43048196
I would like to take it as a reference to cover the following need:
Dual modulation:
1.Keep everything before specific character.
2.Keep everything after the first comma

Example:
-Input: 58dcRoc / xnk Nc jco
-Character: /
-Expected result 1: 58dcRoc
-Expected result 2: xnk Nc jco

I think that the easiest way to manage this need is through a single character, however I would like to go beyond and I was wondering if there is a way to report specific string instead of a single character.

Example
-Input: 58dcRoc / xnk Nc jco
-String: xn
-Expected result 1: 58dcRoc /
-Expected result 2: k Nc jco


Expected result 2: xnk Nc jco
   

If you have questions, please contact me.
ExtractStringAfterCertainChar.xlsm
0
I have an Access program that uses Azure for some tables.  Currently, I simply put customer information into a table (name, address, purchase, very basic stuff) and then our users can go to a website and see that information (I used ASPRunner.net to build what I needed because I have no clue how to do it otherwise).

This was fine but now my client would like to run a report from the Access program, save it in PDF format, and have that available to the customer.  How would I do this?  I read something about BLOB storage but I have never done anything like that.

I finished building the code in Access that will allow the system to create the PDF file and save it in a folder on their local server.
0
Hi

I have an Access form in which I want to put a sub form. I want to show the data in the same format as the table and be able to hide or show certain columns using Access VBA code. The reason for this is that I have to show the sub form on several tabs where different columns are exposed on each tab.

Thanks
0

VBA

12K

Solutions

4K

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.