Procedure too large vba ошибка как исправить - Исправление ошибок и поиск оптимальных решений проблем

I received this error message —Procedure too large — in VBA. What is the reason and way out for this error?

Dirk Vollmar

170k53 gold badges254 silver badges312 bronze badges

asked Sep 20, 2010 at 12:01

You probably have one or more gigantic procedures/functions and I think VBA has a limit of 64k or something per procedure.

You fix it by splitting that procedure up into multiple procedures that can then be called by the one procedure.

So instead of having:

 Sub GiantProcedure()
      ... ' lots and lots of code
 End Sub

You’d have something like:

 Sub GiantProcedure()
      ... ' a little bit of common code
      Proc1()
      Proc2()
      Proc3()

 End Sub

 Sub Proc1()
      ... ' quite a bit of code
 End Sub

 Sub Proc2()
      ... ' quite a bit of code
 End Sub

 Sub Proc3()
      ... ' quite a bit of code
 End Sub

answered Sep 20, 2010 at 12:07

Hans OlssonHans Olsson

53.8k14 gold badges92 silver badges114 bronze badges

The idea of GiantProcedure didn’t work for me, using Microsoft Powerpoint 2013.
Then I added a «call» before each «proc». Like this:

Sub GiantProcedure()

  Call Proc1()
  Call Proc2()
  Call Proc3()

End Sub

Now, it works.

answered Apr 2, 2018 at 16:17

SiamakSiamak

3171 silver badge11 bronze badges

Not sure why the top voted post is recommended as the giant procedure code didn’t work for me either. What did end up working was this:

Sub MacroName

Call ProcedureName1
Call ProcedureName2
etc...

End Sub

Sub ProcedureName1
'insert your vba code here

End Sub

The Call statement pulled each of the subs as long as I called the correct named sub.

answered Dec 6, 2020 at 0:31

Agreed, procedures should be split into smaller subs instead of one gigantic piece of code.

I have seen the Procedure too large errors on 32 bit installations of Excel, when they run / compile perfectly fine on the 64 bit installation the code was written on — so if it is a case of Procedure too large error [suddenly] occurring and it used to work before, check if the Excel installation you are trying to run the code on is 32 bit, and try on a 64 bit installation instead.

answered Feb 16, 2021 at 23:41

Use a 32-bit version of an Office 2010 program
Or Divide the module into parts

answered Oct 6, 2021 at 13:55

My employees get the same message on their weaker computers, but I don’t on mine. So it’s either the fact that the memory on your computer is less than 16GB or otherwise it’s the fact that you have an intel processor that’s too weak. An i7 intel processor that’s after the 8th generation should definitely get the job done. Just get a better computer processor and better memory on your computer and it will work.

answered May 17, 2021 at 20:45

Источник

I’ve spent the whole night doing fruitless «NG» retakes of a long series of Excel VBA scripts, only to encounter repeated issues of Compile Error «Procedure too Large».

As such, I would like to share my story and solution, and would also love to hear from you if you have any better ideas.

Luuklag

3,89911 gold badges39 silver badges56 bronze badges

asked Oct 29, 2013 at 17:11

Pageii StudioPageii Studio

2,0761 gold badge13 silver badges17 bronze badges

I debugged in Visual Basic, and found that by removing the following lines, the problem was resolved:

ActiveWindow.ScrollRow = 10
...
ActiveWindow.ScrollRow = 10000

I realised that as my records had more than 10k rows, Macro also captures each of my mousescrolls or keyboard page-down scrolls as an additional macro command all by itself. As such, it choked up the memory limits for the macro due to multiple downward scrolls. So, the problem was resolved by simply deleting all the lines associated with «ActiveWindow.ScrollRow».

I guess it would have helped if I had used a newer MS version or a more powerful Mac. Suggestions are welcome on how the Macro recording could have been better done, especially on the need to scroll down to capture deep rows. Do note that I did not select the entire column (by clicking on the column headers) to avoid hitting the max limit of the rows (hence adding unnecessary CPU load … I guess). What about folks using newer Office like 2013 … was it worth the upgrade as far as Macros are concerned?

answered Oct 29, 2013 at 17:11

Pageii StudioPageii Studio

2,0761 gold badge13 silver badges17 bronze badges

Источник

I’ve spent the whole night doing fruitless «NG» retakes of a long series of Excel VBA scripts, only to encounter repeated issues of Compile Error «Procedure too Large».

As such, I would like to share my story and solution, and would also love to hear from you if you have any better ideas.

Luuklag

3,89911 gold badges39 silver badges56 bronze badges

asked Oct 29, 2013 at 17:11

Pageii StudioPageii Studio

2,0761 gold badge13 silver badges17 bronze badges

I debugged in Visual Basic, and found that by removing the following lines, the problem was resolved:

ActiveWindow.ScrollRow = 10
...
ActiveWindow.ScrollRow = 10000

answered Oct 29, 2013 at 17:11

Pageii StudioPageii Studio

2,0761 gold badge13 silver badges17 bronze badges

Источник

1 / 1 / 0

Регистрация: 03.12.2011

Сообщений: 47

08.04.2012, 12:10. Показов 25589. Ответов 9

У меня при запуске макроса выдаётся ошибка Procedure too large. Можно обойти эту ошибку,
не разбивая на несколько разных сабов? Процедура действительно большая. А какое ограничение
есть по величине процедур?

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь

15131 / 6405 / 1730

Регистрация: 24.09.2011

Сообщений: 9,999

08.04.2012, 13:15

Процедуру надо оптимизировать. Макрос, записанный макрорекордером, без особых усилий можно сократить в 3-10 раз.
Выложите первые ~50 строк процедуры.

17955 / 7592 / 889

Регистрация: 25.12.2011

Сообщений: 11,324

Записей в блоге: 17

08.04.2012, 13:28

Сообщение было отмечено как решение

Решение

4676 символов вместе с операторами Sub…End Sub, не зависимо от количества пустых строк.

Это значит что процедура действительно чрезмерно перегружена.
Пробуйте удалять лишние Select, Activate.
Пользуйтесь With, циклами, массивами данных.
Создавайте отдельные функции и подпрограммы с передачей им параметров по Byref, Byval.

Придерживаясь элементарных правил, для самого себя Ваш код станет более читаемым, узнаваемым. А отдельные его части можно будет использовать повторно из под других процедур/модулей (т.н. расширяемость программы).

693 / 99 / 10

Регистрация: 25.06.2011

Сообщений: 718

08.04.2012, 13:33

Если внимательно пересмотреть несколько раз код, то всегда есть лишние действия…

1 / 1 / 0

Регистрация: 03.12.2011

Сообщений: 47

08.04.2012, 13:40

[ТС]

эт не в моем случае. у меня множество select case-ов. почему именно 4676. неужели это нельзя где
нить в опшенсах поменять?

1487 / 461 / 52

Регистрация: 10.04.2009

Сообщений: 7,903

08.04.2012, 13:43

бить на процедуры!
и

Сообщение от Казанский

Выложите первые ~50 строк процедуры.

17955 / 7592 / 889

Регистрация: 25.12.2011

Сообщений: 11,324

Записей в блоге: 17

08.04.2012, 14:04

В простейшем случае: найти кусок кода, в который передается и который использует минимум переменных. Вырезать в отдельную подпрограмму.

Сообщение от stickstoff

неужели это нельзя где
нить в опшенсах поменять?

От этого программе легче не станет. Не пытайтесь обмануть компилятор. Это его ограничение по памяти, заложенное в код.

735 / 203 / 11

Регистрация: 23.06.2011

Сообщений: 440

09.04.2012, 09:40

Если не можете оптимизировать процедуру, оптимизируйте исходные данные!
Например «множество Select Case’ов» говорит о том, что прогнило что-то в Датском королевстве.

x-index

0 / 0 / 0

Регистрация: 20.01.2016

Сообщений: 1

11.11.2013, 00:12

Сообщение от Казанский

Visual Basic

Dim address As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
address = Target.address
Select Case address
Case "$E$22"
    Sheets("Номера телефонов").Select
    Sheets("Номера телефонов").Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Sheets("Номера телефонов").Range("B5:K7").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
Case "$E$23"
    Sheets("Номера телефонов").Select
    Sheets("Номера телефонов").Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Sheets("Номера телефонов").Range("B8:K10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
Case "$E$24"
    Sheets("Номера телефонов").Select
    Sheets("Номера телефонов").Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Sheets("Номера телефонов").Range("B11:K13").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

Hugo121

6874 / 2806 / 533

Регистрация: 19.10.2012

Сообщений: 8,553

11.11.2013, 00:30

Думаю можно как-то так — естественно не проверял:

Visual Basic

Dim address As String
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    address = Target.address
    With Sheets("Номера телефонов")
        Select Case address
            Case "$E$22"
                inter1 .Cells
                inter2 .Range("B5:K7")
            Case "$E$23"
                inter1 .Cells
                inter2 .Range("B8:K10")
            Case "$E$24"
                inter1 .Cells
                inter2 .Range("B11:K13")
 
 
        End Select
    End With
End Sub
 
Sub inter1(r)
    With r.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
 
Sub inter2(r)
    With r.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
End Sub

Вернее даже можно чуть компактнее на событии:

Visual Basic

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    address = Target.address
    With Sheets("Номера телефонов")
        Select Case address
            Case "$E$22": inter1 .Cells: inter2 .Range("B5:K7")
            Case "$E$23": inter1 .Cells: inter2 .Range("B8:K10")
            Case "$E$24": inter1 .Cells: inter2 .Range("B11:K13")
        End Select
    End With
End Sub

IT_Exp

Эксперт

87844 / 49110 / 22898

Регистрация: 17.06.2006

Сообщений: 92,604

11.11.2013, 00:30

Помогаю со студенческими работами здесь

Compile error: Procedure too large
Подскажите, как можно оптимизировать данный код?
В конечном файле получается более 300 подобных…

«Invalid procedure call or argument»
Добрый вечер.Просмотрев кучу других тем, та и не смог разобраться со своей проблемой. Подскажите…

Procedure too large Как уменьшить данный код?
Добрый день, уважаемые форумчане!
Прошу помощи, как можно уменьшить код, можно использовать и…

Отметить полужирным шрифтом в исходном тексте слова имеющие приставки «пре», «при», «на», «не»
Отметить полужирным шрифтом в исходном тексте слова имеющие приставки "пре",
"при", "на", "не"….

Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:

Источник

Sub block1(CustRow)
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("I" & CustRow).Value, True   '4.3 Date of Birth Year
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("H" & CustRow).Value, True   '4.2 Date of Birth Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("G" & CustRow).Value, True   '4.1 Date of Birth Month
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("F" & CustRow).Value, True   ' 3. VA file number
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("E" & CustRow).Value, True   '2.3 SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("D" & CustRow).Value, True   '2.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("C" & CustRow).Value, True   '2.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("A" & CustRow).Value, True   '1.2 Lastname
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("B" & CustRow).Value, True   '1.2 Firstname
  Application.Wait Now + 0.00001
'
  Application.SendKeys "{Tab}", True
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("X" & CustRow).Value, True   '10.4 CurrentMailingAddress_ZIPOrPostalCode_FirstFiveNumbers[0]
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("W" & CustRow).Value, True   '10.3 CurrentMailingAddress_StateOrProvince[0]
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("V" & CustRow).Value, True   '10.2 Currentcity[0]
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("U" & CustRow).Value, True   '10.1 CurrentMailingAddress_NumberAndStreet[0]
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AA" & CustRow).Value, True   '11A.3 CLAIMANT'S Last NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("Z" & CustRow).Value, True   '11A.2 CLAIMANT'S Middle NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("Y" & CustRow).Value, True   '11A.1  CLAIMANT'S First NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("L" & CustRow).Value, True   '5.3 CLAIMANT'S Last NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("K" & CustRow).Value, True   '5.2 CLAIMANT'S Middle NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("J" & CustRow).Value, True   '5.1  CLAIMANT'S First NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("O" & CustRow).Value, True   '6.3.SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("N" & CustRow).Value, True   '6.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("M" & CustRow).Value, True   '6.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("P" & CustRow).Value, True   ' 5.Service Number
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AD" & CustRow).Value, True   '11B. Spouse's Date of Birth Year
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AC" & CustRow).Value, True   '11B. Spouse's Date of Birth Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AB" & CustRow).Value, True   '11B. Spouse's Date of Birth Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AG" & CustRow).Value, True   '11C. Spouce's SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AF" & CustRow).Value, True   '11C. Spouce's SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AE" & CustRow).Value, True   '11C. Spouce's SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AJ" & CustRow).Value, True   '11D. Date of Marriage Year
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AI" & CustRow).Value, True   '11D. Date of Marriage Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AH" & CustRow).Value, True   '11D. Date of Marriage Month
  Application.Wait Now + 0.00001
'
  Application.SendKeys "{Tab}", True
  Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "OTHER" Then     '11.F "Other"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "TRIBAL" Then     'TRIBAL
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "PROXY" Then     'PROXY
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "COMMON LAW" Then     'COMMON LAW
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
       Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "RELIGIOUS CEREMONY" Then     'RELIGIOUS CEREMONY
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
          
      
      
  '12A---------------------------------------------------
     Application.Wait Now + 0.00002
      If Range("AP" & CustRow).Value = "NO" Then     '
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{~}"
              Application.Wait Now + 0.00002
              Application.SendKeys "{Tab}", True
      End If
  
  '----------------------------------
  'Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AT" & CustRow).Value, True   '12C. SPOUSE'S SERVICE NUMBE
  Application.Wait Now + 0.00001
   Application.SendKeys "{Tab}", True
  '13A-------------------------------------
   Application.Wait Now + 0.00002
      If Range("AU" & CustRow).Value = "NO" Then     '
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{~}"
              Application.Wait Now + 0.00002
              Application.SendKeys "{Tab}", True
      End If
  '--13-----------------------------------------
  'Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AW" & CustRow).Value, True   '13C.1 CurrentMailingAddress_NumberAndStreet[0]
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AZ" & CustRow).Value, True   '13C.4 CurrentMailingAddress_ZIPOrPostalCode_FirstFiveNumbers[0]
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AY" & CustRow).Value, True   '13C.3 CurrentMailingAddress_StateOrProvince[0]
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AX" & CustRow).Value, True   '13C.2 Currentcity[0]
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  
  '------------------------
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("S" & CustRow).Value, True   '8.3 Telephone No
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("R" & CustRow).Value, True   '8.2 Telephone No
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("Q" & CustRow).Value, True   '8.1 Telephone No
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("T" & CustRow).Value, True   ' 9.email Address
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AO" & CustRow).Value, True   ' 11F.email oTHER
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AM" & CustRow).Value, True   '11E. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AL" & CustRow).Value, True   '11E. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AK" & CustRow).Value, True   '11E. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AV" & CustRow).Value, True   '13B. REASON FOR SEPARATION
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AS" & CustRow).Value, True   '12B.3 SPOUSE'S VA FILE NUMBER_Last 4
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AR" & CustRow).Value, True   '12B.2 SPOUSE'S VA FILE NUMBER_Middle 2
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AQ" & CustRow).Value, True   '12B.1 SPOUSE'S VA FILE NUMBER_First 3
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("E" & CustRow).Value, True   '2.3 SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("D" & CustRow).Value, True   '2.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("C" & CustRow).Value, True   '2.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '-----------14A1---------
  Application.SendKeys Range("BC" & CustRow).Value, True   '14A.1 TO WHOM MARRIED  First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BB" & CustRow).Value, True   '14A.1 TO WHOM MARRIED  Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BA" & CustRow).Value, True   '14A.1 TO WHOM MARRIED  Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '-----------14A2---------
  Application.SendKeys Range("BF" & CustRow).Value, True   '14A2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BE" & CustRow).Value, True   '14A2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BD" & CustRow).Value, True   '14A2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("BI" & CustRow).Value, True   '14A2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BH" & CustRow).Value, True   '14A2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BG" & CustRow).Value, True   '14A2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '-----------14A3---------
  
  Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Annulment" Then     '14.3 "Annulment"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Other" Then     'Other
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys Range("BK" & CustRow).Value, True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
      End If
      
   '--------------------------------------
   '-----------14A4---------
  Application.SendKeys Range("BQ" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BP" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BO" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BN" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BM" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BL" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '--------------------------------------------
  '14B.1 TO WHOM MARRIED
  
  
  Application.SendKeys Range("BT" & CustRow).Value, True   '14B.1 TO WHOM MARRIED Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BS" & CustRow).Value, True   '14B.1 TO WHOM MARRIED Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BR" & CustRow).Value, True   '14B.1 TO WHOM MARRIED First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '--------------------------------------------------
  '-----------14B2---------
  Application.SendKeys Range("BW" & CustRow).Value, True   '14B2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BV" & CustRow).Value, True   '14B2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BU" & CustRow).Value, True   '14B2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("BZ" & CustRow).Value, True   '14B2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BY" & CustRow).Value, True   '14B2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BX" & CustRow).Value, True   '14B2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '-----------------------------------------
  '---14B.3 REASON FOR TERMINATION''
  Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Annulment" Then     '14.3 "Annulment"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Other" Then     'Other
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys Range("CB" & CustRow).Value, True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
       
      End If
      
  '---------------------
  '---14B.4 DATE AND PLACE MARRIAGE TERMINATED---
  Application.SendKeys Range("CH" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CG" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CF" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CE" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CD" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CC" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  '--------------------
  '---14C.1 TO WHOM MARRIED--
  Application.SendKeys Range("CK" & CustRow).Value, True   '14C.1 TO WHOM MARRIED Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CJ" & CustRow).Value, True   '14C.1 TO WHOM MARRIED Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CI" & CustRow).Value, True   '14C.1 TO WHOM MARRIED First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '----------------------------
  
  '---14C.2 DATE AND PLACE OF MARRIAGE
  Application.SendKeys Range("CN" & CustRow).Value, True   '14C2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CM" & CustRow).Value, True   '14C2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CL" & CustRow).Value, True   '14C2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CQ" & CustRow).Value, True   '14C2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CP" & CustRow).Value, True   '14C2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CO" & CustRow).Value, True   '14C2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '-------------------------------
  '--14C.3 REASON FOR TERMINATION
  Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Annulment" Then     '14.C3 "Annulment"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Other" Then     'Other
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys Range("CS" & CustRow).Value, True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
      End If
  
  '------------------------
  '---14C.4 DATE AND PLACE MARRIAGE TERMINATED
  Application.SendKeys Range("CY" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CX" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CW" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CV" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CU" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CT" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '----------------------------------
  '-----------14D2---------
  
  Application.SendKeys Range("DH" & CustRow).Value, True   '14D2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DG" & CustRow).Value, True   '14D2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DF" & CustRow).Value, True   '14D2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DE" & CustRow).Value, True   '14D2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DD" & CustRow).Value, True   '14D2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DC" & CustRow).Value, True   '14D2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '---14D.1 TO WHOM MARRIED--
  Application.SendKeys Range("DB" & CustRow).Value, True   '14D.1 TO WHOM MARRIED Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DA" & CustRow).Value, True   '14D.1 TO WHOM MARRIED Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CZ" & CustRow).Value, True   '14D.1 TO WHOM MARRIED First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '----------------------------
  '---14D.3 REASON FOR TERMINATION ( OTHER)
  Application.SendKeys Range("DJ" & CustRow).Value, True   '14D.3 REASON FOR TERMINATION ( OTHER)
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '--------------------------------
  '--14D.3 REASON FOR TERMINATION
  Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Other" Then     '14.C3 "Other"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Annulment" Then     'Annulment
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
  
  '----14D.4 DATE AND PLACE MARRIAGE TERMINATED ---
  Application.SendKeys Range("DM" & CustRow).Value, True   '14D.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DL" & CustRow).Value, True   '14D.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DK" & CustRow).Value, True   '14D.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  '---VET SSNUMBER
  Application.SendKeys Range("E" & CustRow).Value, True   '2.3 SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("D" & CustRow).Value, True   '2.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("C" & CustRow).Value, True   '2.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
End Sub

Источник