VBA循环遍历指定单元格填充网页表单时Range范围引用错误求解

• 变量使用错误：For Each Row In rng遍历得到的Row是A列对应行的单元格对象，不是数值型行号，直接作为Cells()的第一个参数会导致引用错位，需要调用Row.Row属性获取实际行号。
• Range归属不明确：Set rng = Range("A12:A21")没有指定所属工作表，默认取当前激活的工作表，如果运行时激活的不是Audits表，遍历范围会完全错误。
• 批量填充逻辑缺失：仅打开了一次空白表单，循环时只会反复覆盖同一表单的内容，不会生成多条提交记录，每填完一行需要触发表单提交、等待新的空白表单加载完成后再填充下一行。
• 变量声明不规范：代码中声明了cell As Range但未使用，循环变量Row未做声明，容易触发未定义错误。

Option Explicit
Sub Autofill()
    Dim rng As Range, rowItem As Range
    Dim IE As Object
    Dim ws As Worksheet
    
    ' 绑定Audits工作表，避免激活表错位问题
    Set ws = ThisWorkbook.Sheets("Audits")
    Set rng = ws.Range("A12:A21")
    Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
    
    IE.Visible = True
    ' 首次打开表单页面
    IE.navigate "https://share.amazon.com/sites/IPV/Lists/IPV%20Appeals%20tracker/Issue/newifs.aspx?Source=https%3A%2F%2Fshare%2Eamazon%2Ecom%2Fsites%2FIPV%2FLists%2FIPV%2520Appeals%2520tracker%2FAll%2520items%2Easpx&RootFolder="
    ' 等待页面加载
    WaitForIE IE
    
    For Each rowItem In rng
        Dim curRow As Long
        curRow = rowItem.Row ' 获取当前遍历的行号
        
        ' 填充所有表单字段
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T9").Value = ws.Cells(curRow, 41).Value 'ass tag AO列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T8").Value = ws.Cells(curRow, 39).Value 'man tag AM列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T1").Value = ws.Cells(curRow, 29).Value 'Task ID AC列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T2").Value = ws.Cells(curRow, 6).Value 'mcid F列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T5").Value = ws.Cells(curRow, 3).Value 'country C列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T19").Value = ws.Cells(curRow, 34).Value 'type of audit AH列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T3").Value = ws.Cells(curRow, 38).Value 'site AL列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T4").Value = ws.Cells(curRow, 40).Value 'Marketplace AN列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T6").Value = ws.Cells(curRow, 30).Value 'time AD列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T10").Value = ws.Cells(curRow, 11).Value 'ass act K列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T11").Value = ws.Cells(curRow, 12).Value 'corr act L列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T12").Value = ws.Cells(curRow, 21).Value 'siv act U列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T13").Value = ws.Cells(curRow, 22).Value ' cor siv act V列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_T43").Value = ws.Cells(curRow, 17).Value 'metric name Q列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_D14").Value = ws.Cells(curRow, 24).Value 'siv rfd reaspm X列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_D15").Value = ws.Cells(curRow, 35).Value 'data correctly capt AI列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_D15").Value = ws.Cells(curRow, 27).Value 'if correctly cap AA列
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_D62").Value = "No"
        IE.document.getElementByID("ctl00_ctl33_g_62154394_4b95_41ba_ab7f_81e2f3297aa2_FormControl0_V1_I1_RTC60_RTI1_RT1_newRichText").innerHTML = ws.Cells(curRow, 16).Value 'annot P列
        
        ' 触发表单提交，需要替换下方提交按钮的实际ID，可通过浏览器F12元素选择器获取提交按钮的ID
        ' IE.document.getElementByID("替换为实际提交按钮ID").Click
        
        ' 等待提交完成、新表单加载
        WaitForIE IE
    Next rowItem
    
    ' 可选：运行完成后关闭IE
    ' IE.Quit
    ' Set IE = Nothing
End Sub

' 封装IE加载等待逻辑，避免重复代码
Sub WaitForIE(IE As Object)
    Do While IE.Busy Or IE.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
    Loop
    ' 额外等待1秒保证页面元素渲染完成
    Application.Wait DateAdd("s", 1, Now)
End Sub

• 代码中提交按钮的ID需要你自行替换，打开表单页面按F12用元素选择器点提交按钮就能拿到对应的ID
• 如果不需要自动提交，只是填充完手动确认，可以把提交相关的代码注释掉，填完一行手动提交后再继续运行即可

内容的提问来源于stack exchange，提问作者user12722902