[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

VBA

10K

Solutions

72

Content

i
The collection of articles, videos, and posts on this topic.

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 Guys,

I am getting an error in the following macro. My macro transforms all Vertical Values to horizontal Formatt. Eg:_

1 A B C
2 B E F

then  Output is
1 A
1 B
1 C
2 B
2 E
2 F

But when I try to insert a sheet of 69 columns and 26000 rows it gives runtime error '1004' Below is the code.





Dim a As Variant, b As Variant
Dim i As Long, ii As Long, c As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  a = .Cells(1).CurrentRegion
  ReDim b(1 To (UBound(a, 1) - 1) * (UBound(a, 2) - 1), 1 To 3)
End With
For i = 2 To UBound(a, 1)
  For c = 2 To UBound(a, 2)
    ii = ii + 1
    b(ii, 1) = a(i, 1)
    b(ii, 2) = a(1, c)
    b(ii, 3) = a(i, c)
  Next c
Next i
If Not Evaluate("ISREF(Sheet2!A1)") Then Worksheets.Add(After:=Sheets("Sheet1")).Name = "Sheet2"
With Sheets("Sheet2")
  .UsedRange.ClearContents
  .Cells(1).Resize(UBound(b, 1), UBound(b, 2)) = b
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

its gives error for the following line:
.Cells(1).Resize(UBound(b, 1), UBound(b, 2)) = b

Can anybody help me with this?
0
1 Comment
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Learn More!
In Example1.xlsx, Column "I" has words in each cell like Entire, Shared, SE.1/4, etc.  For every cell in Column "I" that has the word ENTIRE, I would like to add 7 rows below the initial row, copy everything from the initial row into the seven added rows and then renumber column "G"(Space?) from 1 through 8. (See Example2.xlsx).  How would I do this in Excel 2013?
Example1.xlsx
Example2.xlsx
0
1 Comment
I have a form on which I want to do something in records that meet certain criteria.  In my test code below, I loop through the recordset for the form, but my code sets the values for every record to the values appropriate to the first record.  The code below sets Text304 on every record to True if "rst!DateEntered > #9/27/2018 9:02:36 AM#" is true for the first record and every record to false otherwise.

Right now I'm just writing to a text field to diagnose the problem, but what I want to do is to make a field on certain records editable. How do I do that?



Private Sub Form_Load()

    Me.txtTitle.SetFocus
    Dim rst As DAO.Recordset
    Dim fld As Field
 
    Set rst = Me.Recordset
    Do While Not rst.EOF
        If rst!DateEntered > #9/27/2018 9:02:36 AM# Then
            Text304 = "True"
        Else
            Text304 = "False"
        End If
        rst.MoveNext
    Loop
    Me.Refresh
   
End Sub
0
5 Comments
Hello everybody.

I've been dealing with how to run a macro when certain cells change in Excel: I mean, under certain conditions a human being could manually change a value in a cell, and then, this change should trigger a recalculation in another cell. I'd like to precise that in my workbook, for internal reasons, it is no possible to use formulas.

Anyway, up to a certain point, the support Microsoft is clear.

https://support.microsoft.com/en-us/help/213612/how-to-run-a-macro-when-certain-cells-change-in-excel

In particular, if I know in advance the range that has to cause a recalculation, no problem:

Set KeyCells = Range("A1:C10")

Open in new window


Now, my task is harder.

Suppose I've a blank sheet with just a button to run a macro (see attachment "Recalculate-1").

Before running the macro "Update", i don't know what will be the result in terms of populated area.

Suppose, after the running, the new situation as in the second attachment ("Recalculate-2).

I need the green cells triggering recalculation, identified as follows:

- for each value "6" on column B, the green cells have to be - in the same row - from column D to the last populated column in the sheet (in this case, F);
- in event of inserting manually a new value in the green cells, recalculate the sum in column C of the same row (so, suppose I change manually the value in cell E19 from 150 to 100, then the new value in C19 will be 400).

Could anyone help me?
recalculate-1.bmp
recalculate-2.bmp
0
2 Comments
How do you capture a return value from an SQL Stored Procedure run via VBA?

We are using an Access front-end to run a stored procedure on the SQL Server back-end.  It is crashing, so I need to capture the return value from the SP and records if there are any.

I am told we can set-up a parameter when the SP is called.

This is the current code:

stSQL= "EXEC sp_ProcessInvoices @intConsignment = " & me.intIDforSP

set rstSP = new ADODB.recordset
rstSP.activeconnection = fncon 'this is a function that sets the connection string
rstSP. open stSQL

NB: I didn't write the SP so don't know much about it, but I have been told it returns a single value, and sometimes some records.

I think I need to learn:

1) how to setup an output parameter to show the return value
2) if records are returned how to capture them.

I have put a watch on the variable rstSP, but it is currently showing a recordcount of -1 and only a single field.

I appreciate that's rather vague, but if anyone can point me at tutorials that would be a great help.
0
3 Comments
Need help creating a function in SQL Server 2008r2 that loops through records and returns a string value.

I have a function written in VBA that loops through filtered records in a table  and returns a string that i would like to move to SQL.

Public Function NDEOps(sBase As String, sLot As String, sSplit As String, sSub As String, iSSeq As Integer, iESeq As Integer) As String

Dim rst As DAO.Recordset
Dim sqlStr As String
Dim sOpsBetween As String

sOpsBetween = ""

sqlStr = "SELECT dbo_OPERATION.SEQUENCE_NO, dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.SERVICE_ID, dbo_OPERATION.OPERATION_TYPE FROM dbo_OPERATION WHERE dbo_OPERATION.WORKORDER_TYPE='W' AND dbo_OPERATION.WORKORDER_BASE_ID='" & sBase & "' AND dbo_OPERATION.WORKORDER_LOT_ID='" & sLot & "' AND dbo_OPERATION.WORKORDER_SPLIT_ID='" & sSplit & "' AND dbo_OPERATION.WORKORDER_SUB_ID='" & sSub & "' AND dbo_OPERATION.SEQUENCE_NO>" & iSSeq & " And dbo_OPERATION.SEQUENCE_NO<" & iESeq & " Order by dbo_OPERATION.SEQUENCE_NO"
Set rst = CurrentDb.OpenRecordset(sqlStr)
If rst.RecordCount > 0 Then
   rst.MoveFirst
   Do Until rst.EOF
      If sOpsBetween <> "" Then 'not first one
         sOpsBetween = sOpsBetween & ", "
      End If
         sOpsBetween = sOpsBetween & rst("SEQUENCE_NO") & "-" & IIf(rst("OPERATION_TYPE") <> "", rst("OPERATION_TYPE"), IIf(rst("SERVICE_ID") <> "", rst("SERVICE_ID"), rst("RESOURCE_ID")))
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
End If
NDEOps = sOpsBetween

End Function

Open in new window


I would like to redo this logic in SqlServer 2008r2 and have been looking for the fastest solution.  
I am new to creating functions in sql server and see that there are many ways to do this (cursors, Set based queries, temp table with or without identity columns....)

Any help would be greatly appreciated.
0
3 Comments
Excel file example contains 10 columns.
The request is that for all rows where "Motel" is in column A, these rows are moved to Sheet2.
EE-Example.xlsx
0
5 Comments
I need assistance in writing the following formula in VBA

=IFERROR(VLOOKUP(data_lookup!$A$1,CHOOSE({1,2},'Sales'!$G:$G,'Sales'!$B:$B),2,0),"")

Please help
0
9 Comments
Hello All.  This might get more complicated than I originally hoped.

FINAL TASK FOR THIS QUESTION THAT IS NEEDED:
 I am using data from a MS Access Table to populate a MS Word Table.
     acro_spacer and  acronym

One of two  "Custom Styles"  must be added to each row of the MS Word Table when the data is added.
i was hoping that someone could provide with the correct syntax?


     If strtxtStatusAAW = "Add" Then
        objDoc.TrackRevisions = True
        strTrackRevisions = True
        tblAppA.Rows(intrsttblAppendixAWorkingRowCounter).Select
        tblAppA.Cell(intrsttblAppendixAWorkingRowCounter, 1).Range.Text = strttxtTermAAW
        tblAppA.Cell(intrsttblAppendixAWorkingRowCounter, 2).Range.Text = strttxtDefinitionAAW
       
      tblAppA.Rows(intrsttblAppendixAWorkingRowCounter).Selection.Range.Style = "acro_spacer"   BTW. Did not work.

    End If

Please hang in there with me.
This is where the possible problems start.  When I create a new document and Add the Table using VBA, The Custom Styles are not in the Styles Choice List.
They are in the list of previous documents that have used the custom styles.  See Photo Attached.

Next Questions:
Can I use VBA to Add them to the Styles List just by saying Add Style acro_spacer or acronym and then use them?
         objDoc.HeadingStyles.Add Style:="acro_spacer", Level:=2    BTW. Did not work.

Do I have to create them from scratch and then add them to the Styles List using VBA  before I …
0
5 Comments
my vba below is copying and pasting data from one sheet to another.
I need assistance in pasting it to the next empty row. I keep getting an error msg when attempting to modify. (the macro below just paste from A2)

Sub Copy()
Dim i As Long
Dim lngLastRow As Long, lngPasteRow As Long

'Find the last row to search through
lngLastRow = Sheets("Data").Range("D65535").End(xlUp).Row

'Initialize the Paste Row
lngPasteRow = 2

For i = 2 To lngLastRow
    If Sheets("Data").Range("D" & i).Value = "test1" Then
        Sheets("Data").Select
        Range("A" & i & ":IV" & i).Copy
        Sheets("Cars").Select
        Range("A" & lngPasteRow & ":IV" & lngPasteRow).Select
        ActiveSheet.Paste
        lngPasteRow = lngPasteRow + 1
    End If
Next i

End Sub
0
5 Comments
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Learn More!
need add an selector with date of high

a55555.PNG
to get this

a888.PNG29121680--2-_4N_to_3N_selector.xlsm
0
11 Comments
I need to know if it possible to loop through a recordset(tblViewNames) comparing one table to another.  I need to be able to output column that don't exist in the BI View tables from the CR tables . I have a sample query created in Teradata  where it shows BI columns missing from the CR Tables comparison.  Then I need to output this missing columns to a table(tblFinalOutput).
td_example.xlsx
Edge_Ticket.accdb
0
10 Comments
in 4N to create count sheet for DTA 2N
like we did
 here : https://www.experts-exchange.com/questions/29120916/color-data-count.html
29121337a--1-_4N_w_2N_3N_to_count_2.xlsm
0
2 Comments
Hello All.
I need to change the Height of of the Rows of a MS Word Table.
Below is the code that creates and changes the Width of the columns.

strAppendixAName = "Appendix_A.docx"
strAppendixAPath = CurrentProject.Path
strAppendixAFullPath = strAppendixAPath & "\" & "Appendix_A.docx"

Dim objWord
Dim objDoc
Dim objSelection

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
Set objSelection = objWord.Selection

objWord.Visible = True
objDoc.SaveAs (strAppendixAFullPath)

 objDoc.Select
 objDoc.Activate
 objDoc.ActiveWindow.Activate

'If The Selected Document Is Not Open, A Message Box Will Open Telling The User To Open The Selected File.
If objDoc Is Nothing Then 'The Selected Document Is Not Open.
    MsgBox "Please Open The Selected Document" & vbCrLf & vbCrLf & strAppendixAPath, vbCritical, "Document Is Not Open"
    PID = Shell("explorer.exe " & strAppendixAPath, vbNormalFocus) 'If The Document Is Not Open, This Will Open The File Explorer To The Correct Folder.
    Exit Sub
Else '
End If

objSelection.TypeText ("Appendix A" & vbCrLf & "Total items:  " & inttblAppendixAWorking & vbCrLf & vbCrLf)

inttblAppendixAWorking = 10
intRows = inttblAppendixAWorking
intCols = 2

 'Create Table.  This moves the Table below any data that is in the document.
Set tblAppA = objDoc.Tables.Add(Range:=objDoc.Bookmarks("\EndOfDoc").Range, _
                                                    NumRows:=intRows, _
       …
0
2 Comments
Good morning,

In my textbox4 i succed to give a format to the number like 1 254 254  but when i apply it to my textbox3 It doesn't work. I want it with decimals in my textbox 3

Thank you
Draft.xlsm
0
15 Comments
Hello,

In the attached file-worksheet DETAILS
These are the main columns
LOGON_ID,ASSOCIATE_CONTRACTORS, JOB_CODE, CSDB_ENTITLEMENT

If you filter by the Job code, you will see all Unique values of CSDB entitlements
alloted to that job code.
If filter by user, you will see all the entitlement assigned to that user. This will not
tell us if any of the missing or unassigned entitilement which we discussed earlier as Unique.

Looking for a formula or a macro which will give the user's missing and matching entitlements in the following format

LOGON_ID      ASSOCIATE_CONTRACTOR      JOB_CODE CSDB_ENTITELMENT MISSING ENTITLEMENT

e.g
If you fitler job code and select CZ0202. In this job code we have 2 users John Smith and Naomi Morris
we get 11 CSDB_ENTITLEMENTS
ALFA21
ALFA22
ALFA23
SIGMA13
SIGMA13
SIGMA14
SIGMA17
SIGMA18
BOSS22
BOSS23
BOSS24

I have put in the the report in the REPORT tab, to explain what I need.

Thank you for your help. I appreciate it
ENT_RECORDS.xlsx
0
4 Comments
I have data in 'Sheet1' Cells D100:AA:10000.
What VBA do I need that does the following:

1) Assign this data to an SQL string sorted by column E.
2) Paste the results on sheet 2.
0
1 Comment
I had this question after viewing Modify default height of drop-down filter menu in Excel.

I manage an addin to Excel that I've created in vba. I'd like to add a custom option under the filter drop-down menu. On the referenced case the question was if the poup size could be set programmatically. Now I'm interested in adding a new custom option to the menu. Can the menu be manipulated in any way at all?
0
2 Comments
How do I loop thru a folder called October to load all the files found within the folder.

I know what to do with the file once I locate the file - I have code to import the file.  I am somewhat familiar with the FileSystem Object, but I am struggling with:

Once I identify the folder how do I proceed to loop thru to take the next file until empty ?
0
3 Comments
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.

Learn More!
I have a VBA COUNTIF statement that will not evaluate a value within a cell unless I go into the call and re-type the cell.  The cell value is text.  As an example in the cell is 10.10.2018.  If I go into the cell and re-type in the value (note I was sent the spreadsheet, I did not type the original value).

When I set a variable to the cell it correctly displays "10.10.2018" (before any re-type.  Thus, why am I forced to re-type in the value in order for the CountIf to recognize the value ?

 Count = Application.CountIf(Cells(5, i), "10.10.2018")
0
9 Comments
HI.  I have a shared access database that tracks returned goods and actions taken\pending.  I had some old email code that sent a separate email to each user where action was required.  This means a lot of email processing.  I am changing that to send out a single distinct email for action users and they can  now log into the database to view a report showing their specific actions.  To make this possible,  I turned all of the email alert code into a series update queries that  write to a table called RGAlerts.  I can run the refresh manually but I'd really like to refresh it each time the users either open or close the report, I'm not sure which would be best.  I have all of this working except for the report-driven table refresh.  I'm concerned about user updating simultaneously.  It would only hurt this one table that will get refreshed anyway but I think it could cause some confusion as that table could show duplicate entries if users happen to trigger the refresh at the same time..  
What is a good way to manage simultaneous updating in ACCESS 2010 and 2016?  Both clients are in place.  Is record locking possible to use in this situation?

thanks
0
6 Comments
I need to know if it possible to loop through a recordset(tblViewNames) comparing one table to another.  I need to be able to output column that don't exist in the BI View tables from the CR tables . I have a sample query created in Teradata  where it shows BI columns missing from the CR Tables comparison.  Then I need to output this missing columns to a table(tblFinalOutput).
Edge_Ticket.accdb
td_example.xlsx
0
4 Comments
In MS Access there is a main form with a sub form. They are linked by a common customer field. The issue is when the source query of the main form has no records for the particular customer chosen. An empty form results. How would I set this up so that when the main form source query has no records for a customer a message box advising the user that this customer has no records appears and then close the form ?
0
3 Comments
I have a display function that will display data from my worksheet to a locked textbox, Now i want to build upon my code to only display if the cells in column 
D = Environ("Username")

Open in new window


The code i am currently using for displaying the code is 
Private Sub CommandButton39_Click()

    Range("B1:B37").Select
    ActiveWorkbook.Worksheets("FUN1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("FUN1").Sort.SortFields.Add Key:=Range("B1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FUN1").Sort
        .SetRange Range("A1:D37")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Me.TextBox4.Value = ""
Dim wb As Workbook
Workbooks("Working.xlsm").Activate
Dim arrIn As Variant
Dim arrOut()
Dim i As Long
Dim j As Long

    arrIn = Sheets("FUN1").Range("A1").CurrentRegion
    
    ReDim arrOut(1 To UBound(arrIn))
    
    For i = 1 To UBound(arrIn)
    
        For j = 1 To UBound(arrIn, 2) - 1
            arrOut(i) = arrOut(i) & arrIn(i, j) & vbTab
        Next j
        
        arrOut(i) = arrOut(i) & arrIn(i, j)
    
    Next i
    
    With TextBox4
        .MultiLine = True
        .Value = Join(arrOut, vbCrLf)
    End With
End Sub

Open in new window
0
3 Comments
is there an easy way in excel to use a column in a separate sheet of data as a data filter criteria. I have a sheet of data (sheet 1 with over 250k rows) and one of the columns is recipient-address (which represents an email address). I want to filter the data in sheet 1 recipient-address column to only show rows for certain email addresses, which are those which I have saved on another sheet (sheet 2) column A (about 70 email addresses). What is the best way to approach this?
0
4 Comments

VBA

10K

Solutions

72

Content

i
The collection of articles, videos, and posts on this topic.

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.