vba excel宏“需要对象”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20190802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-12 00:38:06 来源:igfitidea点击:
excel macro "object required" error
提问by Ricardo Alves
I'm working for the first time with VBA and I got an error that I can't seem to correct.
我是第一次使用 VBA，但出现了一个似乎无法纠正的错误。
I want to pass some fields from one Sheet to another. It gives me "object required" but I can't seem to figure out out so sold this...
我想将一些字段从一张工作表传递到另一张工作表。它给了我“需要的对象”，但我似乎无法弄清楚所以卖掉了这个......
My code is this:
我的代码是这样的：
Sub principal_main()
Dim Max_rows, j, row_number As Integer
Dim i, count As Integer
Dim name_Row As String
Dim morada, localidade, email, cod_postal, data As String
Dim telefone, nif As Integer
Dim escreve As Range
count = 0
row_number = 12
name_Row = ""
Max_rows = 23200
For i = 13 To Max_rows
If name_Row = "" Then
name_Row = myWorksheet.Range("B" & i).Value
morada = ""
localidade = ""
email = ""
cod_postal = ""
data = ""
telefone = 0
nif = 0
count = 0
Else
count = count + 1
If count = 26 Then
name_Row = ""
End If
Select Case count
Case 2
morada = myWorksheet.Range("B" & i).Value
telefone = myWorksheet.Range("I" & i).Value
Case 4
localidade = myWorksheet.Range("B" & i).Value
email = myWorksheet.Range("I" & i).Value
Case 5
cod_postal = myWorksheet.Range("B" & i).Value
nif = myWorksheet.Range("I" & i).Value
Case 7
data = myWorksheet.Range("C" & i).Value
nome_row = Replace(nome_row, " - ", "")
For j = 0 To 10
nome_row = Replace(nome_row, j, "")
Next
Set Sheets("final").Range("B" & count_copiados) = name_Row
Set Sheets("final").Range("C" & count_copiados) = morada
Set Sheets("final").Range("D" & count_copiados) = cod_postal
Set Sheets("final").Range("E" & count_copiados) = localidade
Set Sheets("final").Range("F" & count_copiados) = telefone
Set Sheets("final").Range("G" & count_copiados) = email
Set Sheets("final").Range("H" & count_copiados) = nif
Set Sheets("final").Range("I" & count_copiados) = data_ficha
'Sheets("final").Row(count_copiados).collumn("B") = name_Row
'Sheets("final").Row(count_copiados).collumn("C") = morada
'Sheets("final").Row(count_copiados).collumn("D") = cod_postal
'Sheets("final").Row(count_copiados).collumn("E") = localidade
'Sheets("final").Row(count_copiados).collumn("F") = telefone
'Sheets("final").Row(count_copiados).collumn("G") = email
'Sheets("final").Row(count_copiados).collumn("H") = nif
'Sheets("final").Row(count_copiados).collumn("I") = data_ficha
End Select
End If
Next
End Sub
new code, with some changes:
新代码，有一些变化：
Option Explicit
Sub principal_main()
Dim Max_rows, row_number As Long
Dim i, j, count, count_copiados As Long
Dim name_Row As String
Dim morada, localidade, email, cod_postal, data_ficha As String
Dim telefone As Variant
Dim nif As Integer
Dim escreve As Range
Dim myWorksheet As Worksheet
myWorksheet = Sheets(1)
count = 0
row_number = 12
name_Row = ""
Max_rows = 23200
count_copiados = 13
For i = 13 To Max_rows
If name_Row = "" Then
name_Row = myWorksheet.Range("B" & i).Value
morada = ""
localidade = ""
email = ""
cod_postal = ""
data_ficha = ""
telefone = 0
nif = 0
count = 0
Else
count = count + 1
If count = 26 Then
name_Row = ""
End If
Select Case count
Case 2
morada = myWorksheet.Range("B" & i).Value
telefone = myWorksheet.Range("I" & i).Value
Case 4
localidade = myWorksheet.Range("B" & i).Value
email = myWorksheet.Range("I" & i).Value
Case 5
cod_postal = myWorksheet.Range("B" & i).Value
nif = myWorksheet.Range("I" & i).Value
Case 7
data_ficha = myWorksheet.Range("C" & i).Value
name_Row = Replace(name_Row, " - ", "")
For j = 0 To 10
name_Row = Replace(name_Row, j, "")
Next
Set Sheets("final").Range("B" & count_copiados) = name_Row
Set Sheets("final").Range("C" & count_copiados) = morada
Set Sheets("final").Range("D" & count_copiados) = cod_postal
Set Sheets("final").Range("E" & count_copiados) = localidade
Set Sheets("final").Range("F" & count_copiados) = telefone
Set Sheets("final").Range("G" & count_copiados) = email
Set Sheets("final").Range("H" & count_copiados) = nif
Set Sheets("final").Range("I" & count_copiados) = data_ficha
'Sheets("final").Row(count_copiados).collumn("B") = name_Row
'Sheets("final").Row(count_copiados).collumn("C") = morada
'Sheets("final").Row(count_copiados).collumn("D") = cod_postal
'Sheets("final").Row(count_copiados).collumn("E") = localidade
'Sheets("final").Row(count_copiados).collumn("F") = telefone
'Sheets("final").Row(count_copiados).collumn("G") = email
'Sheets("final").Row(count_copiados).collumn("H") = nif
'Sheets("final").Row(count_copiados).collumn("I") = data_ficha
count_copiados = count_copiados + 1
End Select
End If
Next
End Sub
采纳答案by Ricardo Alves
try
尝试
Option Explicit
Sub principal_main()
Dim Max_rows&, row_number&
Dim i&, j&, count&, count_copiados&
Dim name_Row$
Dim morada$, localidade$, email$, cod_postal$, data_ficha$
Dim telefone As Variant
Dim nif&
Dim escreve As Range
Dim myWorksheet As Worksheet
Set myWorksheet = Sheets(1)
count = 0
row_number = 12
name_Row = ""
Max_rows = 23200
count_copiados = 13
For i = 13 To Max_rows
If name_Row = "" Then
name_Row = myWorksheet.Range("B" & i).Value
morada = ""
localidade = ""
email = ""
cod_postal = ""
data_ficha = ""
telefone = 0
nif = 0
count = 0
Else
count = count + 1
If count = 26 Then
name_Row = ""
End If
Select Case count
Case 2
morada = myWorksheet.Range("B" & i).Value
telefone = myWorksheet.Range("I" & i).Value
Case 4
localidade = myWorksheet.Range("B" & i).Value
email = myWorksheet.Range("I" & i).Value
Case 5
cod_postal = myWorksheet.Range("B" & i).Value
nif = myWorksheet.Range("I" & i).Value
Case 7
data_ficha = myWorksheet.Range("C" & i).Value
name_Row = Replace(name_Row, " - ", "")
For j = 0 To 10
name_Row = Replace(name_Row, j, "")
Next
Sheets("final").Range("B" & count_copiados) = name_Row
Sheets("final").Range("C" & count_copiados) = morada
Sheets("final").Range("D" & count_copiados) = cod_postal
Sheets("final").Range("E" & count_copiados) = localidade
Sheets("final").Range("F" & count_copiados) = telefone
Sheets("final").Range("G" & count_copiados) = email
Sheets("final").Range("H" & count_copiados) = nif
Sheets("final").Range("I" & count_copiados) = data_ficha
'Sheets("final").Row(count_copiados).collumn("B") = name_Row
'Sheets("final").Row(count_copiados).collumn("C") = morada
'Sheets("final").Row(count_copiados).collumn("D") = cod_postal
'Sheets("final").Row(count_copiados).collumn("E") = localidade
'Sheets("final").Row(count_copiados).collumn("F") = telefone
'Sheets("final").Row(count_copiados).collumn("G") = email
'Sheets("final").Row(count_copiados).collumn("H") = nif
'Sheets("final").Row(count_copiados).collumn("I") = data_ficha
count_copiados = count_copiados + 1
End Select
End If
Next
End Sub
