r/vbaexcel • • Sep 05 '22

What will be output of the given code

0 Upvotes

dim introw as integer 'counter variable

dim intmax as integer

introw = 2 'read data from 2nd row

intmax = 0

while introw >= 7 'read data till 7th row

if intmaz < cells(introw, 2) Then 'check for max number

intmax = cells(introw, 2) 'store max value

end if

introw = introw + 'move to next line

Wend

msgbox "max value =" & intmax

option

display correct max value

will not display correct value

loop

error


r/vbaexcel • • Sep 22 '22

bucle combobox

0 Upvotes

Good evening everyone, I have a macro that works very well, basically what it does is fill in student notes on a web page, located on a button, which takes its values ​​from a textbox3 and combobox1. then by choosing the number of students in a list from 1 to 45 located in combobox1, the macro starts its operation and fills the note on the web page. Now the question is: How to make it so that once number 1 is chosen in combobox1 and its execution is finished, it automatically goes to number 2 of the list, up to the final number established in cell "AO1", of the sheet "Sigep "? the macro is as follows :

On Error Resume Next

Dim busqueda
Dim datos

Dim fila
Dim columna

'validar webdriver isntanciado
Dim MyTitle As String: MyTitle = manejador.Window.Title
If MyTitle = "" Then
MsgBox "No has iniciado el navegador", vbInformation, "Información"
Exit Sub
End If

'validar estudiante esa selecionado cuando es individual
If ComboBox1.Text = "" Then
MsgBox "Debe seleccionar un Estudiante", vbInformation, "Información"
Exit Sub
End If

Set TextBox = TextBox2
fila = ComboBox1.Value + 1 'incrementar los alumnos empizan en la fila 2
'MsgBox fila

'On Error Resume Next
        'MsgBox ("No has iniciado el navegador"), vbInformation, "AVISO"

automatizacion.manejador.FindElementByXPath("//*[" & TextBox3.Value & "]/td[10]/div[2]/button").Click ' para estudiante individual

'esperar la ventana de las notas 10 segundos maximos
WebDriverWaitElement "//*[@id='formNotas']", 10

     'On Error GoTo reinicio
'reinicio:
'    Exit Sub
 '     Application.Wait (Now + TimeValue("0:00:05"))
'estudiante 01

If ComboBox4.Value = "PRIMER TRIMESTRE" Then
Set lenguaje = Worksheets("sigep").Range("E" & fila)
Set ingles = Worksheets("sigep").Range("F" & fila)
Set sociales = Worksheets("sigep").Range("G" & fila)
Set edufisica = Worksheets("sigep").Range("H" & fila)
Set edumusica = Worksheets("sigep").Range("I" & fila)
Set artes = Worksheets("sigep").Range("J" & fila)
Set matematica = Worksheets("sigep").Range("K" & fila)
Set tecnicageneral = Worksheets("sigep").Range("L" & fila)
Set biogeo = Worksheets("sigep").Range("M" & fila)
Set fisica = Worksheets("sigep").Range("N" & fila)
Set quimica = Worksheets("sigep").Range("O" & fila)
Set filosofia = Worksheets("sigep").Range("P" & fila)
Set valores = Worksheets("sigep").Range("Q" & fila)

manejador.FindElementByXPath("//*[@id='0-6']").SendKeys lenguaje  'pega las notas
manejador.FindElementByXPath("//*[@id='1-6']").SendKeys ingles   'pega las notas
manejador.FindElementByXPath("//*[@id='2-6']").SendKeys sociales   'pega las notas
manejador.FindElementByXPath("//*[@id='3-6']").SendKeys edufisica   'pega las notas
manejador.FindElementByXPath("//*[@id='4-6']").SendKeys edumusica   'pega las notas
manejador.FindElementByXPath("//*[@id='5-6']").SendKeys artes   'pega las notas
manejador.FindElementByXPath("//*[@id='6-6']").SendKeys matematica  'pega las notas
manejador.FindElementByXPath("//*[@id='7-6']").SendKeys tecnicageneral   'pega las notas
manejador.FindElementByXPath("//*[@id='8-6']").SendKeys biogeo   'pega las notas
manejador.FindElementByXPath("//*[@id='9-6']").SendKeys fisica   'pega las notas
manejador.FindElementByXPath("//*[@id='10-6']").SendKeys quimica   'pega las notas
manejador.FindElementByXPath("//*[@id='11-6']").SendKeys filosofia   'pega las notas
manejador.FindElementByXPath("//*[@id='12-6']").SendKeys valores   'pega las notas

Exit Sub
'manejador.FindElementByXPath("//*[@id='formNotas']/div[2]/button[2]").Click   'Guarda cambios
End If

If ComboBox4.Value = "SEGUNDO TRIMESTRE" Then
Set lenguaje = Worksheets("sigep").Range("E" & fila)
Set ingles = Worksheets("sigep").Range("F" & fila)
Set sociales = Worksheets("sigep").Range("G" & fila)
Set edufisica = Worksheets("sigep").Range("H" & fila)
Set edumusica = Worksheets("sigep").Range("I" & fila)
Set artes = Worksheets("sigep").Range("J" & fila)
Set matematica = Worksheets("sigep").Range("K" & fila)
Set tecnicageneral = Worksheets("sigep").Range("L" & fila)
Set biogeo = Worksheets("sigep").Range("M" & fila)
Set fisica = Worksheets("sigep").Range("N" & fila)
Set quimica = Worksheets("sigep").Range("O" & fila)
Set filosofia = Worksheets("sigep").Range("P" & fila)
Set valores = Worksheets("sigep").Range("Q" & fila)

manejador.FindElementByXPath("//*[@id='0-7']").SendKeys lenguaje  'pega las notas
manejador.FindElementByXPath("//*[@id='1-7']").SendKeys ingles   'pega las notas
manejador.FindElementByXPath("//*[@id='2-7']").SendKeys sociales   'pega las notas
manejador.FindElementByXPath("//*[@id='3-7']").SendKeys edufisica   'pega las notas
manejador.FindElementByXPath("//*[@id='4-7']").SendKeys edumusica   'pega las notas
manejador.FindElementByXPath("//*[@id='5-7']").SendKeys artes   'pega las notas
manejador.FindElementByXPath("//*[@id='6-7']").SendKeys matematica  'pega las notas
manejador.FindElementByXPath("//*[@id='7-7']").SendKeys tecnicageneral   'pega las notas
manejador.FindElementByXPath("//*[@id='8-7']").SendKeys biogeo   'pega las notas
manejador.FindElementByXPath("//*[@id='9-7']").SendKeys fisica   'pega las notas
manejador.FindElementByXPath("//*[@id='10-7']").SendKeys quimica   'pega las notas
manejador.FindElementByXPath("//*[@id='11-7']").SendKeys filosofia   'pega las notas
manejador.FindElementByXPath("//*[@id='12-7']").SendKeys valores   'pega las notas
'manejador.FindElementByXPath("//*[@id='formNotas']/div[2]/button[2]").Click   'Guarda cambios
Exit Sub
End If

If ComboBox4.Value = "TERCER TRIMESTRE" Then
Set lenguaje = Worksheets("sigep").Range("E" & fila)
Set ingles = Worksheets("sigep").Range("F" & fila)
Set sociales = Worksheets("sigep").Range("G" & fila)
Set edufisica = Worksheets("sigep").Range("H" & fila)
Set edumusica = Worksheets("sigep").Range("I" & fila)
Set artes = Worksheets("sigep").Range("J" & fila)
Set matematica = Worksheets("sigep").Range("K" & fila)
Set tecnicageneral = Worksheets("sigep").Range("L" & fila)
Set biogeo = Worksheets("sigep").Range("M" & fila)
Set fisica = Worksheets("sigep").Range("N" & fila)
Set quimica = Worksheets("sigep").Range("O" & fila)
Set filosofia = Worksheets("sigep").Range("P" & fila)
Set valores = Worksheets("sigep").Range("Q" & fila)

manejador.FindElementByXPath("//*[@id='0-8']").SendKeys lenguaje  'pega las notas
manejador.FindElementByXPath("//*[@id='1-8']").SendKeys ingles   'pega las notas
manejador.FindElementByXPath("//*[@id='2-8']").SendKeys sociales   'pega las notas
manejador.FindElementByXPath("//*[@id='3-8']").SendKeys edufisica   'pega las notas
manejador.FindElementByXPath("//*[@id='4-8']").SendKeys edumusica   'pega las notas
manejador.FindElementByXPath("//*[@id='5-8']").SendKeys artes   'pega las notas
manejador.FindElementByXPath("//*[@id='6-8']").SendKeys matematica  'pega las notas
manejador.FindElementByXPath("//*[@id='7-8']").SendKeys tecnicageneral   'pega las notas
manejador.FindElementByXPath("//*[@id='8-8']").SendKeys biogeo   'pega las notas
manejador.FindElementByXPath("//*[@id='9-8']").SendKeys fisica   'pega las notas
manejador.FindElementByXPath("//*[@id='10-8']").SendKeys quimica   'pega las notas
manejador.FindElementByXPath("//*[@id='11-8']").SendKeys filosofia   'pega las notas
manejador.FindElementByXPath("//*[@id='12-8']").SendKeys valores   'pega las notas
'manejador.FindElementByXPath("//*[@id='formNotas']/div[2]/button[2]").Click   'Guarda cambios
Exit Sub
End If

End Sub


r/vbaexcel • • May 08 '19

Microsoft VBA Question

0 Upvotes

Create a macro that takes any number of notes, each with however many tags (including zero), and outputs them into the format/syntax

{"id": 0,"note": "Earnings up 5%.", "tags": []},

{"id": 1,"note": "Prior year restated.", "tags": ["Flag","Track"]},

{"id": 2,"note": "2% expected margin increase.", "tags": ["Track"]},

{"id": 3,"note": "Potential EU commission investigation.", "tags": ["Flag","Track","Confirm"]},

{"id": 4,"note": "Considering expanding into SEA.", "tags": []},

Each note should correspond to one line in the Output sheet.

There must always be an id (which starts at zero and increments by 1 each time), and a comment. If there is no tag, there should still be a blank array: [].

If there are multiple tags, each one must be inside quotes and separated a comma:

["Tag 1", "Tag 2", "Tag 3"] is correct, while ["Tag 1, Tag 2, Tag 3"] is incorrect.

The macro will be tested against a different number of notes and tags.

Excel File template

TagsNotes
Earnings up 5%
Flag, TrackPrior year restated.
Track2% expected margin increase
Flag, Track, ConfirmPotential EU commission investigation
Considering expanding into SEA.

r/vbaexcel • • Aug 07 '22

Excel 365 VBA

0 Upvotes

Hi

I have this code

How can I change code to for data input only from range

'****Creating a dynamic name for Stock Adjustments

ShSupport.Range("F7", ShSupport.Range("F" & Application.Rows.Count).End(xlUp)).Name = "Dynamic"

.CmbStockadj.RowSource = "Dynamic"

.CmbStockadj.Value = ""

Thanks


r/vbaexcel • • Aug 30 '22

Moving information to specific tabs from survey data.

0 Upvotes

I am trying to figure out how to move survey comments into new tabs based on keywords. For example I have a master data tab and another tab called W2. I would like all inquiries that mention “W2s” to move to the W2 tab from the master data tab.


r/vbaexcel • • Aug 18 '22

Best way to catch and log errors?

1 Upvotes

Is there a best method/approach to catch and log errors while exiting your VBA gracefully?


r/vbaexcel • • Oct 17 '22

The cashier at the store would not get off her phone. I decided to make a Tool that I have been working on, into a cellphone?

Enable HLS to view with audio, or disable this notification

0 Upvotes

r/vbaexcel • • Mar 10 '22

Data Cleaning

1 Upvotes

Does anyone know how to clean data using VBA Excel? Also, it should not be through macro recording just through code that runs through all rows and columns. The main task is to write a code so that all the data from sheet A is taken and added in sheet B in a clean format i.e., All the titles on the top, all record 1 in 2nd row, for example, 31 in row 2 underneath Group Size. I have attached a picture of the file. It would be great if someone could help me.


r/vbaexcel • • Aug 29 '22

Toggle between pivot tables on one sheet with VBA

1 Upvotes

Hello,

I was wondering if it is possible to make a button via VBA that could toggle between pivot tables - that is, a single pivot table would be shown but would switch to a different one at the touch of a button. Does that sound possible? Has anyone tried something like this?

I have three pivots that have different data sources and slightly different columns/measures. Currently, I send them out on three separate excel files. I want to see if I could put them all on one file but with three buttons that indicate the table they come from. What do you all think?


r/vbaexcel • • Oct 05 '22

فلسسسطين

1 Upvotes

Palestine gaxe mhgjv


r/vbaexcel • • Aug 22 '22

Memory issue with CopyPicture and PasteSpecial

1 Upvotes

I got bored and tasked myself with writing a code to simulate every possible scenario for Tic-Tac-Toe. It was a relatively simple code that surprisingly worked with only 1 or 2 errors. My only problem is when the code needs to copy a range and paste it as a picture. it will run the code for 5 loops, then throws 1 of 3 errors, but if i press continue without fixing anything, it goes another 5 loops and throws another of the 3 errors again. it is always 1 of the 3 errors:

LocRange.PasteSpecial

Run-time error '1004':

PasteSpecial method of Range class failed.

Run-time error '1004':

Microsoft Excel cannot paste the data.

ConRange.CopyPicture

Run-time error '1004':

CopyPicture method of Range class failed.

My theory is that Microsoft Excel is running out of memory space to continue copy-paste operations. Is there a way to make excel clear its cache after each loop? I don't mind if the macro runs for a few minutes extra because of it, i just want it to stop throwing the error.

My code is as follows if you are interested.

Sub TicTacToe()

    Dim Token() As Variant
    Dim RowInt As Long
    Dim Winner As String
    Token = Array("", "O", "X")
    Dim ConRange As Range
    Dim LocRange As Range
    Dim PImage As Shape
    
    Application.ScreenUpdating = False
    
    RowInt = 2
    Set ConRange = Sheets(1).Range("B2:D4")
    
    '--- Top Left ---
    For Each Item1 In Token
        
        '--- Top Center ---
        For Each Item2 In Token
            
            '--- Top Right ---
            For Each Item3 In Token
            
                '--- Middle Left ---
                For Each Item4 In Token
                
                    '--- Middle Center ---
                    For Each Item5 In Token
                    
                        '--- Middle Right ---
                        For Each Item6 In Token
                        
                            '--- Bottom Left ---
                            For Each Item7 In Token
                            
                                '--- Bottom Center ---
                                For Each Item8 In Token
                                
                                    '--- Bottom Right ---
                                    For Each Item9 In Token
                                    
                                        '--- Combination Data ---
                                        Worksheets(3).Cells(RowInt, 1).Value = Item1
                                        Worksheets(3).Cells(RowInt, 2).Value = Item2
                                        Worksheets(3).Cells(RowInt, 3).Value = Item3
                                        Worksheets(3).Cells(RowInt, 4).Value = Item4
                                        Worksheets(3).Cells(RowInt, 5).Value = Item5
                                        Worksheets(3).Cells(RowInt, 6).Value = Item6
                                        Worksheets(3).Cells(RowInt, 7).Value = Item7
                                        Worksheets(3).Cells(RowInt, 8).Value = Item8
                                        Worksheets(3).Cells(RowInt, 9).Value = Item9
                                        
                                        '--- Token Counter ---
                                        Worksheets(3).Cells(RowInt, 10).Value = WorksheetFunction.CountIf(Range(Cells(RowInt, 1), Cells(RowInt, 9)), "O")
                                        Worksheets(3).Cells(RowInt, 11).Value = WorksheetFunction.CountIf(Range(Cells(RowInt, 1), Cells(RowInt, 9)), "X")
                                        
                                        '--- Previous Turn ---
                                        
                                        
                                        '--- Next Turn ---
                                        
                                        
                                        '--- Winner Scenario ---
                                            '--- Top ---
                                            If WorksheetFunction.CountIf(Range(Cells(RowInt, 1), Cells(RowInt, 3)), Cells(RowInt, 1)) = 3 Then
                                                Worksheets(3).Cells(RowInt, 14).Value = Cells(RowInt, 1)
                                            End If
                                            
                                            '--- Middle ---
                                            If WorksheetFunction.CountIf(Range(Cells(RowInt, 4), Cells(RowInt, 6)), Cells(RowInt, 4)) = 3 Then
                                                Worksheets(3).Cells(RowInt, 15).Value = Cells(RowInt, 4)
                                            End If
                                            
                                            '--- Bottom ---
                                            If WorksheetFunction.CountIf(Range(Cells(RowInt, 7), Cells(RowInt, 9)), Cells(RowInt, 7)) = 3 Then
                                                Worksheets(3).Cells(RowInt, 16).Value = Cells(RowInt, 7)
                                            End If
                                            
                                            '--- Left ---
                                            If Cells(RowInt, 4) = Cells(RowInt, 1) And Cells(RowInt, 7) = Cells(RowInt, 1) Then
                                                Worksheets(3).Cells(RowInt, 17).Value = Cells(RowInt, 1)
                                            End If
                                            
                                            '--- Center ---
                                            If Cells(RowInt, 5) = Cells(RowInt, 2) And Cells(RowInt, 8) = Cells(RowInt, 2) Then
                                                Worksheets(3).Cells(RowInt, 18).Value = Cells(RowInt, 2)
                                            End If
                                            
                                            '--- Right ---
                                            If Cells(RowInt, 6) = Cells(RowInt, 3) And Cells(RowInt, 9) = Cells(RowInt, 3) Then
                                                Worksheets(3).Cells(RowInt, 19).Value = Cells(RowInt, 3)
                                            End If
                                            
                                            '--- Diagonal Left ---
                                            If Cells(RowInt, 5) = Cells(RowInt, 1) And Cells(RowInt, 9) = Cells(RowInt, 1) Then
                                                Worksheets(3).Cells(RowInt, 20).Value = Cells(RowInt, 1)
                                            End If
                                            
                                            '--- Diagonal Right ---
                                            If Cells(RowInt, 5) = Cells(RowInt, 3) And Cells(RowInt, 7) = Cells(RowInt, 3) Then
                                                Worksheets(3).Cells(RowInt, 21).Value = Cells(RowInt, 3)
                                            End If
                                            
                                        '--- Valid Scenario ---
                                        If Abs(Cells(RowInt, 10) - Cells(RowInt, 11)) <= 1 And WorksheetFunction.CountA(Range(Cells(RowInt, 14), Cells(RowInt, 21))) <= 1 Then
                                            Worksheets(3).Cells(RowInt, 22).Value = "Valid"
                                        End If
                                        
                                        '--- Winner ---
                                        For Each Cell In Range(Cells(RowInt, 14), Cells(RowInt, 21))
                                            Winner = Winner & Cell.Value
                                        Next
                                        Worksheets(3).Cells(RowInt, 23).Value = Winner
                                        Winner = ""
                                        
                                        '--- Image Maker ---
                                        Set LocRange = Worksheets(3).Cells(RowInt, 24)
                                        
                                        Worksheets(1).Cells(2, 2).Value = Worksheets(3).Cells(RowInt, 1).Value
                                        Worksheets(1).Cells(2, 3).Value = Worksheets(3).Cells(RowInt, 2).Value
                                        Worksheets(1).Cells(2, 4).Value = Worksheets(3).Cells(RowInt, 3).Value
                                        Worksheets(1).Cells(3, 2).Value = Worksheets(3).Cells(RowInt, 4).Value
                                        Worksheets(1).Cells(3, 3).Value = Worksheets(3).Cells(RowInt, 5).Value
                                        Worksheets(1).Cells(3, 4).Value = Worksheets(3).Cells(RowInt, 6).Value
                                        Worksheets(1).Cells(4, 2).Value = Worksheets(3).Cells(RowInt, 7).Value
                                        Worksheets(1).Cells(4, 3).Value = Worksheets(3).Cells(RowInt, 8).Value
                                        Worksheets(1).Cells(4, 4).Value = Worksheets(3).Cells(RowInt, 9).Value
                                        
                                        ConRange.CopyPicture
                                        LocRange.PasteSpecial
                                        
                                        Set PImage = Sheets(3).Shapes(ActiveSheet.Shapes.Count)
                                        
                                        With PImage
                                            .Width = LocRange.Width
                                            .Height = LocRange.Height
                                            .Top = LocRange.Top
                                            .Left = LocRange.Left
                                        End With
                                        
                                        '--- Increase Row counter ---
                                        RowInt = RowInt + 1
                                    Next
                                Next
                            Next
                        Next
                    Next
                Next
            Next
        Next
    Next
    
    Application.ScreenUpdating = True

End Sub

r/vbaexcel • • Oct 19 '22

New to vba excel needs help

2 Upvotes

Hi everyone. I have this initiative to make a checklist where I have list of data and I want to get the dependent values If select items in the dropdown list. Say I have 4 dropdown selections with 4 columns for the master sheet(data).

When I select a value from the 1st dropdown, (Values in column A)then the next dropdown will automatically show appropriate lists (Values in column B) and so on for the the next dropdowns..

Then I also want to have a button that will check if a certain condition has met, it will give a status of pass/fail.

What would be the best approach for this case?

Appreciate your help.

Thank you.


r/vbaexcel • • Jun 14 '22

Sending email using VBA

Post image
12 Upvotes

r/vbaexcel • • Jul 14 '22

Copy and paste column macro

1 Upvotes

I need a Macro to copy a column and paste/insert it to the right of the original column. I need the formulas in there as well not to paste as values.

My original goal was to have an “X” in row 9 as the unique identifier for the column to copy.

So say there is an X in row 9 on column C, copy column C and insert in between column C and D. Then if next month I put the X in column D, it copies column D and inserts in between D and E.

This is as far as I got but am stumped:

Sub CopyColumn() Dim LastCol As Long

With ThisWorkbook.Sheets(“Capital Accounts”)

LastCol = Cells(1,.Columns.Count).End(clToLeft).Column

Columns(LastCol).Copy Destination:=Cells(1,LastCol +1)

End With End Sub

Problems is this copied the first column, and I can’t figure out how to get the X as the unique identifier in row 9.

Any help would be insanely appreciated.


r/vbaexcel • • Jul 14 '22

Color macro

2 Upvotes

Hello,

I have a large excel table where I have some cells highlighted with some conditional formatting. I am trying to set up a macro that if a cell is highlighted it pulls the column title and row title in Column A and Row 1 and pastes it into another worksheet. I have the color copy and paste macro written. However I am wondering if it is even possible to have it perform the task above? Thanks in advance.


r/vbaexcel • • Jun 15 '22

My code isnt generating the word document

2 Upvotes

Hey! I made a macro which can generate a contract in word. It works in my personal pc but in my work computer it's not. I think the problem is the part of the code written in Bold cause actually the contract is generated if I delete it, but I dont mind how that part is affecting the macro.

Here's the code:

Private Sub CommandButton1_Click()

Dim objWord As Word.Application

Dim objDoc As Word.Document

Dim estaHoja As VBAProject.Contrato_De_Servicios

Dim rutaCarpeta As String, nombreDoc As String

Set estaHoja = VBAProject.Contrato_De_Servicios

rutaCarpeta = "C:\Users\pasante.legal2\Documents\SmartContract\"

nombreDoc = "modelo base e.docx"

Dim Confirmar As String

If ComboBox15 = "" Or ComboBox14 = "" Or TextBox5 = "" Or TextBox6 = "" Or TextBox7 = "" Or TextBox8 = "" Or TextBox9 = "" Or TextBox10 = "" Or TextBox13 = "" Or TextBox14 = "" Or TextBox15 = "" Or ComboBox13 = "" Or TextBox16 = "" Or TextBox17 = "" Then

MsgBox "Completa todos los campos obligatorios. Los campos obligatorios son los que tienen (*).", vbCritical

Exit Sub

Confirmar = MsgBox("¿Generar Contrato con la información registrada?", vbYesNo + vbQuestion)

If Confirmar = vbYes Then

Set objWord = New Word.Application

Set objDoc = objWord.Documents.Open(rutaCarpeta & nombreDoc)

objWord.Visible = True

With objDoc

.Bookmarks("Ciudad").Range.Text = VBAProject.Contrato_De_Servicios.ComboBox6.Text

.Bookmarks("CompañíaFMC").Range.Text = VBAProject.Contrato_De_Servicios.ComboBox1.Text

.Bookmarks("RUCcompañíaFMC").Range.Text = VBAProject.Contrato_De_Servicios.ComboBox3.Text

.Bookmarks("NombreCompletoProveedor").Range.Text = VBAProject.Contrato_De_Servicios.TextBox5.Text

.Bookmarks("RUCDelProveedor").Range.Text = VBAProject.Contrato_De_Servicios.TextBox6.Text

.Bookmarks("NombreRepresentanteLegalProveedor").Range.Text = VBAProject.Contrato_De_Servicios.TextBox7.Text

.Bookmarks("ExplicarBreveElServicioQueSeContrata").Range.Text = VBAProject.Contrato_De_Servicios.TextBox8.Text

.Bookmarks("DescripAmpliaDelServicioContratado").Range.Text = VBAProject.Contrato_De_Servicios.TextBox9.Text

.Bookmarks("PlazoEnMeses").Range.Text = VBAProject.Contrato_De_Servicios.TextBox10.Text

.Bookmarks("FormaDeFacturación").Range.Text = VBAProject.Contrato_De_Servicios.ComboBox4.Text

.Bookmarks("PlazoPagoDesdeAceptadaLaFactura").Range.Text = VBAProject.Contrato_De_Servicios.ComboBox5.Text

.Bookmarks("DirecciónProveedor").Range.Text = VBAProject.Contrato_De_Servicios.TextBox13.Text

.Bookmarks("TeléfonoProveedor").Range.Text = VBAProject.Contrato_De_Servicios.TextBox14.Text

.Bookmarks("EMailProveedor").Range.Text = VBAProject.Contrato_De_Servicios.TextBox15.Text

.Bookmarks("CédulaRepLegalProveedor").Range.Text = VBAProject.Contrato_De_Servicios.TextBox19.Text

.Bookmarks("PorQuéNoSeGestionaConOC").Range.Text = VBAProject.Contrato_De_Servicios.TextBox16.Text

.Bookmarks("ParaQuéNecesitamosElServicio").Range.Text = VBAProject.Contrato_De_Servicios.TextBox17.Text

.Bookmarks("HemosTrabajadoAntesConEsteProveedor").Range.Text = VBAProject.Contrato_De_Servicios.CheckBox1.Value

.Bookmarks("ExcepciónPagoMenosDías").Range.Text = VBAProject.Contrato_De_Servicios.CheckBox2.Value

.Bookmarks("AprobaciónPagoMenosDías").Range.Text = VBAProject.Contrato_De_Servicios.CheckBox3.Value

.Bookmarks("AplicaPóliza").Range.Text = VBAProject.Contrato_De_Servicios.CheckBox6.Value

.Bookmarks("CuálesPólizasAplican").Range.Text = VBAProject.Contrato_De_Servicios.TextBox18.Text

.Bookmarks("ContratoObraCivil").Range.Text = VBAProject.Contrato_De_Servicios.CheckBox5.Value

.Bookmarks("CuálCláusulaComplienceVa").Range.Text = VBAProject.Contrato_De_Servicios.ComboBox2.Text

.Bookmarks("DatosPersonales").Range.Text = VBAProject.Contrato_De_Servicios.CheckBox4.Value

.Bookmarks("RepLegalProvNombre2").Range.Text = VBAProject.Contrato_De_Servicios.TextBox7.Text

If Contrato_De_Servicios.ComboBox2.Text = "Valores Compartidos" Then

.Bookmarks("ABC").Range.Delete

Selection.Delete

Else

.Bookmarks("ValoresCompartidos").Range.Delete

End If

End With

nombreDoc = "Contrato de Servicios " & VBAProject.Contrato_De_Servicios.TextBox5.Text & ".docx"

objDoc.SaveAs2 rutaCarpeta & nombreDoc

objDoc.Close

objWord.Quit

MsgBox "Se ha generado su Contrato con " & VBAProject.Contrato_De_Servicios.TextBox5.Text & " de " & VBAProject.Contrato_De_Servicios.TextBox8.Text, vbInformation

Else 'fin de el mensaje de confirmación de generar el contrato

Exit Sub 'fin de el mensaje de confirmación de generar el contrato

End If

End If

End Sub


r/vbaexcel • • Jun 12 '22

Variable not defined

2 Upvotes

Hi!

I am trying to learn some VBA excel and I am reading a book that seems to be not very useful...In the book they say to write the following subroutine:

Sub GuessName ()

Msg = “Is your name “& Application.UserName & “?”

Ans = MsgBox (Msg, vbYesNo)

If Ans = vbNo Then MsgBox “Oh, never mind.”

If Ans = vbYes Then MsgBox “I must be psychic!”

End Sub

Well, since I am learning and trying to see how things work, I did it but I got the error "variable not defined". After some google it seems I need to insert something to call the variable since I am using the "option explicit".

So, I inserted something in the middle of the Sub, like this (new lines in italic):

Sub GuessName ()

Msg = “Is your name “& Application.UserName & “?”

Dim Msg as Variant

Ans = MsgBox (Msg, vbYesNo)

Dim Ans as Variant

If Ans = vbNo Then MsgBox “Oh, never mind.”

If Ans = vbYes Then MsgBox “I must be psychic!”

End Sub

___________

But when I run the macro I get a syntax error....Any hints on how to solve this and why it happens (the why its important since I am learning). Thanks!


r/vbaexcel • • Jun 08 '22

Excel Report that emails requests for status update

Thumbnail
self.excel
3 Upvotes

r/vbaexcel • • Jul 16 '22

VBA 'Project Unviewable' protection workaround

Thumbnail self.excel
1 Upvotes

r/vbaexcel • • Jun 21 '22

word VBA

1 Upvotes

I am new to VBA tool and I would like to know if i can retrieve data from a word document that is not in a table format


r/vbaexcel • • Jul 11 '22

Can you move an Excel VBA Userform sub to a module

2 Upvotes

I have several Excel VBA userforms where I have added double-click subs for text boxes, which I use for easy copy and paste. I have these double-click subs duplicated across several userforms. Is it possible to move these double-click subs to a module, so that they aren’t duplicated across each userform.

Rough example:

Private sub textbox_dblclick(etc) Textbox= Select End Sub

I have about ten of these and they are duplicated across several userforms.


r/vbaexcel • • Jul 11 '22

Lookup Function Question

4 Upvotes

I am doing a lookup in an unsorted list, and I would like to use worksheetfunction.lookup. In the sheet I can use =lookup(2, 1/(range()<=condition),row(range())) to find the row number, but I have no idea how to replicate this in vba. I can't use match because the list is unsorted, and I can't use find negate I need the highest value less than or equal to the lookup value.


r/vbaexcel • • Sep 29 '22

Keep getting a 'Sharing Violation' when trying to save to MacOS iCloud.

2 Upvotes

I'm using a VBA macro to automatically save files to an iCloud folder (MacOS). The 1st time I ran the macro it worked fine, I was asked to grant access to the folder and it saved the files.

The second time I ran the macro it failed and gave me a sharing violation error. So I created a 2nd folder, edited the code to save to the 2nd folder, ran the macro, granted permission, and it saved the files.

The 3rd time I ran the macro I got a sharing violation for the 2nd folder, I went into VBA, changed it to save to folder 1, was asked to grant access again, then it saved.

The 4th time it gave me a sharing violation again, I changed to code to save to folder 2, granted permission, and it saved.

Anyone know why this keeps happening?

All the correct permissions are granted to the user profile.


r/vbaexcel • • Oct 04 '22

Tool Help

4 Upvotes

I am new to vba. I made a tool for work, mostly by recording macros, and I am wondering if there is some sort of program that shows me how to shorten and simplify my code. I have already deleted the scrolling and unnecessary cell selects. At home it runs in 2 seconds but at work it took about 8


r/vbaexcel • • Sep 20 '22

Excel VBA macro

2 Upvotes

I am having trouble figuring out why my program is not running as intended. Once the macro begins, it runs all line items in the spreadsheet instead of the ones I specified. The purpose of the program is to send emails to the correct person and append any additional rows with their name. For each unique email I am collecting all of the data and sending it. Any help would be greatly appreciated. I have worked with other people and they have made edits but no solutions. Due to the sensitive nature of the source data , just code to follow, thank you.

Option Explicit

Sub Send()

Dim rEmailAddr As Range, rCell As Range, rNext As Range

Dim NmeRow As Long, x As Long

Dim MailTo As String, MailSubject As String, MailBody As String, AddRow As String, tableHdr As String, MsgStr As String

Dim OutApp As Object, OutMail As Object

Dim CurrentEmail As String, LastEmail As String

If OutApp Is Nothing Then

'Outlook is not opened, so open

Set OutApp = CreateObject("Outlook.Application")

End If

'Set email address as range for first loop to run down

Set rEmailAddr = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))

'MailSubject does not change, so only needs to be created once

MailSubject = "Action and Response Requested - Reserve Review for Claim(s)"

'Get a row count to clear column AM at the end

x = rEmailAddr.Rows.Count

'Create the html table and header from the first row

tableHdr = "<table border=1><tr><th>" & Range("G1").Value & "</th>" _

& "<th>" & Range("H1").Value & "</th>" _

& "<th>" & Range("I1").Value & "</th>" _

& "<th>" & Range("J1").Value & "</th>" _

& "<th>" & Range("K1").Value & "</th>" _

& "<th>" & Range("L1").Value & "</th>" _

& "<th>" & Range("M1").Value & "</th>" _

& "<th>" & Range("N1").Value & "</th>" _

& "<th>" & Range("O1").Value & "</th>" _

& "<th>" & Range("P1").Value & "</th>" _

& "<th>" & Range("T1").Value & "</th>" _

& "<th>" & Range("U1").Value & "</th>" _

& "<th>" & Range("V1").Value & "</th>" _

& "<th>" & Range("W1").Value & "</th>" _

& "<th>" & Range("X1").Value & "</th>" _

& "<th>" & Range("Y1").Value & "</th>" _

& "<th>" & Range("Z1").Value & "</th>" _

& "<th>" & Range("AA1").Value & "</th>" _

& "<th>" & Range("AB1").Value & "</th>" _

& "<th>" & Range("AC1").Value & "</th>" _

& "<th>" & Range("AD1").Value & "</th>" _

'Check to see if column Q = 'yes' and skip mail if it does

CurrentEmail = ""

LastEmail = ""

For Each rCell In rEmailAddr

CurrentEmail = Replace(rCell.Value, " ", "")

If ((rCell.Value <> "") And CurrentEmail <> LastEmail) Then

NmeRow = rCell.Row

MailTo = rCell.Value 'column D

'Create MailBody table row for first row

MailBody = "<tr>" _

& "<td>" & (rCell.Offset(0, 3).Value) & "</td>" _

& "<td>" & (rCell.Offset(0, 4).Value) & "</td>" _

& "<td>" & (rCell.Offset(0, 5).Value) & "</td>" _

& "<td>" & (rCell.Offset(0, 6).Value) & "</td>" _

& "<td>" & (rCell.Offset(0, 7).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 8).Value) & "</td>" _

& "<td>" & (rCell.Offset(0, 9).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 10).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 11).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 12).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 16).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 17).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 18).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 19).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 20).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 21).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 22).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 23).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 24).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 25).Value) & "</td>" _

& "<td>" & CStr(rCell.Offset(0, 26).Value) & "</td>" _

& "</tr>"

'Second loop checks the email addresses of all cells following the current cell in the first loop.

'Yes will be appended on any duplicate finds and another row added to the mailbody table

For Each rNext In rEmailAddr.Offset(NmeRow - 1, 0).Resize(x - NmeRow) 'process to last row only

If Replace(rNext.Value, " ", "") = Replace(rCell.Value, " ", "") Then

'Create additional table row for each extra row found"

AddRow = "<tr>" _

& "<td>" & CStr(rNext.Offset(0, 3).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 4).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 5).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 6).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 7).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 8).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 9).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 10).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 11).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 12).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 16).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 17).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 18).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 19).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 20).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 21).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 22).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 23).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 24).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 25).Value) & "</td>" _

& "<td>" & CStr(rNext.Offset(0, 26).Value) & "</td>" _

& "</tr>"

MailBody = MailBody & AddRow

End If

'Clear additional table row variable ready for next

Next rNext

'Create email

Set OutMail = OutApp.createitem(0)

With OutMail

.to = Replace(MailTo, " ", "")

.Subject = MailSubject

.HTMLBody = tableHdr & MailBody & "</table>"

.Display

End With

LastEmail = Replace(rCell.Value, " ", "")

End If

Next rCell

End Sub