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

Hi! I need help with this code.

I need to make a code so that it will fill in "week1"  for the cells in row 3,  14, 25 etc.

As of now it does not work properly. Please give some advice

Sub AddTableContents()

Dim week1 As Integer

week1 = 3 Or 14 Or 25 Or 36 Or 47 Or 58 Or 69 Or 80 Or 91 Or 102 Or 113 Or 124 Or 134

If Cells(week1, 7) Then
Cells(week1, 7) = "Week1"
End If

End Sub

Open in new window


Furthermore, this is the original code but it  did not do anything

Sub AddTableContents()

Dim week1 As Integer

week1 = 3 Or 14 Or 25 Or 36 Or 47 Or 58 Or 69 Or 80 Or 91 Or 102 Or 113 Or 124 Or 134

Cells(week1, 7) = "Week1"


End Sub

Open in new window

0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

column highlighted in red colour nothing to do with that and we don't have to consider the red highlighted colour column, No role of that data
the coloumn highlighted in yellow colour we have to work on that
copy that data and paste that data to sheet2 each first empty row of each  row
in sheet2 also we don't have to consider the red highlighted coloumn no role of that coloumn
I have posted the question  before also  the code was proper but my data  is alpha numeric in red highlighted coloumn so that code does't work so that's y don’t consider red highlighted coloumn
see the sample file
Book1.xlsm
0
Hello Sirs,

Below Code was working perfect but now having problem while saving file as PDF, it still save the file but without any extension e.g. .pfd or .xlsb, to open the file i have choose the file type then only I can open it.


 
Dim FileExtStr As String, pdfName As String, filesavename As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim FilePath As String
    Dim FileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False

        Set Sourcewb = ActiveWorkbook
        Select Case MsgBox("Do you want to save as XLS/PDF, Yes for Xls, No for PDF", vbYesNo Or vbExclamation Or vbDefaultButton1, "File format")
        
        Case vbYes
            'Copy the sheet to a new workbook
            ActiveSheet.Copy
            Set Destwb = ActiveWorkbook

            'Determine the Excel version and file extension/format
            With Destwb
                If Val(Application.Version) < 12 Then
                    'You use Excel 97-2003
                    FileExtStr = ".xls": FileFormatNum = -4143
                Else
                    'You use Excel 2007-2016
                    Select Case Sourcewb.FileFormat
                    Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                    Case 52:
                        If .HasVBProject Then
                            FileExtStr = ".xlsm": FileFormatNum = 52
                        End If
                

Open in new window

0
Hi! I need help to convert a "For Next" loop to a "Do While" Loop.

As of Now, the "For Next" Loop works perfectly but when I converted it to a "Do While" Loop, it does not work.

Please help to edit the "Do While" Loop so it works.

I have attached an example workbook where the codes should be used :)
ForNext Version:
Sub ForNext()

Dim row1, row2, counter As Integer

For row1 = 3 To 10

For row2 = 2 To 553

    If Cells(row2, 3).Value = " Su Mura" And _
    Cells(row2, 2).Value = Cells(1, 7).Value And _
    Cells(row2, 5).Value = Cells(row1, 7).Value Then
    counter = Cells(row2, 4).Value + counter
    
Cells(row1, 8).Value = counter
counter = 0
    
    End If
    Next
    Next
    


End Sub

Open in new window


Do While Version
Sub DoWhileVersion()

Dim row1, row2, counter As Integer
row1 = 3
row2 = 2

Do While row1 < 11


Do While row2 < 554

    If Cells(row2, 3).Value = " Su Mura" And _
    Cells(row2, 2).Value = Cells(1, 7).Value And _
    Cells(row2, 5).Value = Cells(row1, 7).Value Then
    counter = Cells(row2, 4).Value + counter
    
Cells(row1, 8).Value = counter

counter = 0
End If
    
row1 = row1 + 1
Loop

row2 = row2 + 1
Loop



End Sub

Open in new window

NewProjectBook.xlsm
0
Hi

In Access VBA is it possible to pull in exchange rates? For instance the $/Euro exchange rate.
0
How do I find the [default] application printer in VBA Access 10 so that I can set the bottom margin.

eg

Dim Ptr as printr
ptr=application.printer  ??
ptr.BottomMargin=0.5

Open in new window


Thanks in advance

Clive
0
Modify the Vba code
Vba Code with data attachment-the vba code works fine in this file but my data is modified so look into the Now my file is modified attachment
Now my file is modified attachment- this is my modified data i need the vba code for this file to do the same thing which vba code with data attachment has done copy and paste the data of sheet1 and paste to  first empty cell of each row
Vba-Code-with-data.xlsm
Now-my-file-is-modified-and-it-look.xlsm
0
this highlighted colour is only for understanding purpose
Conditionaly copy the data from sheet1 and paste to sheet2
Kindly see the sample file
i have to do this only by vba
Sample128.xlsm
0
I have an Excel spreadsheet with a named range which feeds into a combo box in Word.  I need to have multiple copies of this combo box which takes the data from the same named range in Excel.  The problem I am experiencing is that VBA automatically populates the first combo box but does not populate the other combo boxes.

Private Sub Document_New()
'Populates combo box with contents of an Excel named range.
 Dim xlapp As Object
 Dim xlbook As Object
 Dim xlsheet As Object
 Dim i As Long
 Dim subject As String
 Dim bStart As Boolean
 Dim ffield As FormField
 Dim oCC As ContentControl
   On Error Resume Next
   Set xlapp = GetObject(, "Excel.Application")
   If Err Then
      bStart = True
      Set xlapp = CreateObject("Excel.Application")
   End If
   On Error GoTo 0
   Set xlbook = xlapp.Workbooks.Open("C:\combo file.xlsx")
   Set xlsheet = xlbook.Worksheets(1)
   With xlsheet.Range("A1")
      Set oCC = ActiveDocument.SelectContentControlsByTitle("TestComboBox").Item(1)
        For i = 2 To .CurrentRegion.Rows.Count
          Debug.Print .Offset(i - 1, 0)
          oCC.DropdownListEntries.Add Text:=.Offset(i - 1, 0), Value:=.Offset(i - 1, 0)
        Next i
   End With
   xlbook.Close
   If bStart = True Then
     xlapp.Quit
   End If
End Sub

Open in new window

0
I am creating a code that would make all odd rows yellow and all even rows white

It's not working properly right now so I would appreciate any help editing it! The error is that the subscript is out of range

Sub loops_exercise()

Dim R As Integer
    R = 1
    Do While R < 20
   
Dim C As Integer
For C = 1 To 20

       If R Mod 2 = 0 Then
           Cells(R, C).Interior.ColorIndex = RGB(255, 0, 0)
       Else
       
           Cells(R, C).Interior.ColorIndex = RGB(2, 0, 0)
       End If
       
Next

R = R + 1
Loop
    
End Sub

Open in new window

0
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

EE29096722.vbs

See attached VBA Script that was developed for my E-Mail Solution..

Currently it writes out errors and emails sent to the same log file..

I would like to separate this log file so that I have two files - 1. EMails Sent & 2. Errors Logged

Maybe the filename of these two files you append the date stamp at the end of the log file name
0
Help me edit this code to achieve this end result. As much as possible, please keep the format the same for "select case" and "Loop"
Sub QuestionFive()

    Dim r, c As Integer
Do While r < 20
    r = 1
Do While c < 20
    c = 1


          
Select Case Cells(20, 20)
Case (r + c) Mod 2
    Cells(r, c).Interior.Color = RGB(255, 255, 0)
     Cells(r, c).Interior.Color = RGB(255, 255, 255)

End Select

r = r + 1
Loop

c = c + 1
Loop
End Sub

Open in new window


de.JPG
0
I am looking to build a VBA macro that will take columns A and B in the attached sample and copy and paste each name/ username into the blank areas.
sample.xlsx
0
Hallo,
I am looking for a solution how to delete my files in a depository at once. I made a list in excel (column A) with files with their full names, which I want to delete from this depository:
H:\Dokument\ToClean\12354-aabb.pdf
H:\Dokument\ToClean\12222-aaggh.zip
My files for deleting are in various forms (pdf, word, zip, doc) and have various names.

Until now I could find makro, vba in Excel only for deleting 1. all files from the depository, 2.files with a specific format 3. a specific file. Non of these solutions are appropriate for me.
One solustion was supposed  to be suitable, but it does not work:

Sub DeleteButton()
Dim i As Long
 
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Kill Cells(i, 1).Value
    Next i

End Sub

This makro has a problem with the "Kill Cells(i, 1).Value". I would be thankfull if you could help.
Thank you.
0
copy the data from sheet1 and paste the data to sheet2 conditional
plz see the sample file
i have to do all this by vba only
Book1.xlsm
0
if a sheet has duplicates rows then put the summary in sheet2
I need this type of result
all this I have to do by vba only
this highlighted colour is only for understanding purpose
see the sample file
Book1.xlsm
0
what I want is to combine sheet1,sheet2,sheet3,sheet4,sheet5 into 1sheet
so for the same
I have putted sheet1 ,sheet2,sheet3,sheet4,sheet5  all cells data in the same place after putting (,)
so all this I have to do by vba
Sample-file.xlsm
0
Hi All,

There is an urgent requirement for excel macro which will extract the required data from the multiple analysis report(more than 500 excel files) and generate one invoice excel file (Filename: Invoice).

input data to extract
**Fix rows and columns in each analysis file.**
Rows 23 to 34

and required columns are
Column B and C

Sample input data in input excel files

and required output
Column B data | Column C data | Discounted Rates (Need to add by code)| Amount in USD (Need to add by code)
Please find attached sample output file

Thanks,
Shailesh
Analysis_1.xlsx
Analysis_2.xlsx
Invoice.xlsx
0
Originally posted here:

https://stackoverflow.com/questions/50329940/vbs-convert-tab-delimited-csv-file-to-xlsx

I have already posted a similar question before which converts CSV files to xlsx.

This question can be found here:

https://stackoverflow.com/questions/49294151/vbs-loop-through-multiple-csv-files-in-a-folder-and-convert-the-files-to-xls

Code: VBS

'Constants 
Const xlOpenXMLWorkbook = 51             '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50                     '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56                       '(97-2003 format in Excel 2007-2016, xls)

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

strFolder = "B:\EE\EE29088597\Files"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
        ' Display to console each 

Open in new window

0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I have a VBA project with several userforms and modules that's evolved over many years. As of today, PowerPoint 2016 on Windows fails to load the pptm project without error and I've tracked it down to one userform in which all of its ~20 controls except one (a MultiPage control) have disappeared and hence the numerous corresponding control events code, which is still present, is complaining.

When I open the project in PowerPoint 2013 or 2010 the userform appears correctly and the project compiles. I have exported the userform from 2010, deleted it from the project in 2016 and re-imported it into the project in 2016. The userform still appears to be empty apart from the one MultiPage control and the project doesn't compile.

This userform isn't the only one using a MultiPage control so I don't think that's the issue. If I remove the userform from the project and the associated .Show method elsewhere, the project compiles normally.

What is going on?!

PowerPoint 2016 PC version 1806 build 10205.20009
0
I need to sequence records in a Access table based on a field (Provider Name). All like Provider Names should have the same sequence number. For example if the are 5 Dickson INCs then they should have the same sequence number. Can you provide sample code? Maybe use an update query. Thanks
0
need count  as show

a23.PNG

here te file to
c_to_hor_.xlsx
0
Need Excel VBA to force all cells to UPPERCASE even for cells that don't contain text only.
0
I need to create individual Excel workbooks representing provider name based on an Access table. I need to loop through the table and create individual workbooks base on like provider names (provider name is a field in the table). I would like to save to the workbooks to a specified folder with the name of the provider. I have attached the sample table . Can you provide some sample code? Thanks
AccessTable.xlsx
0
I have an Access query that I want to export regularly to a specific sheet in Excel.  This sheet has a chart that is dependent upon the cells' data.  

I am using the following command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query Name", "C:\test\test.xlsx", True, "Sheet with Chart"

However, it exports to Sheet "_Sheet with Chart".

It always creates a new sheet, named with an underscore immediately preceding whatever sheet name I choose.  In some instances, it will put a '1' immediately after a sheet name - for example, if I use "Sheet1" in the command, the resulting action is a new sheet with "Sheet11" showing the newly exported data.  

Is there a way to have it export to an existing sheet that I name?  Is the command different?  Is there a setting in Excel that needs to be changed?  Or is this not even possible?  Any help is appreciated.
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.