VBA IF条件，错误仅适用于第一个循环

Question

我正在尝试使用VBA创建一个图形。首先，我有一个仪表板，我想有一个主工作表，总结一些我想要搜索的名字，所以我所有的数据都在其他工作表。

我试图创建一个搜索特定名称的VBA IF条件，数据表中的名称如下:

数据表考虑到这一点，我决定" I "在数据表中找到我想要的名字。当有人想要搜索名称1或名称2时……，此人将在主工作表中选择姓名，其中单元格为:"C3"如果主表中的C3单元格等于数据表B2或Q2…然后创建一个图形。

If Worksheets("data").Cells(2, i) = Worksheets("main").Cells(3, 3) Then

问题是这个条件只适用于名字，我不知道为什么。还有一个错误说:

运行时错误"13": Type mismatch

我拥有的完整vba代码:´

Sub graph()
Dim chrt As ChartObject
Dim i As Long
Dim LastRow As Long
Dim LastColumn As Long
'Find the last used row
LastRow = Sheets("vol").Range("B65536").End(xlUp).Row
'Find the last used column
LastColumn = 500
'Looping from second row till last row which has the data
For i = 2 To LastColumn
If Worksheets("vol").Cells(2, i) = Worksheets("main").Cells(3, 3) Then
'Sheet main is selected bcoz charts will be inserted here
Sheets("main").Select
ActiveSheet.Shapes.AddChart.Select
'this adds the chart and selects it in the same statement
ActiveChart.ChartType = xlLine
'now the line chart is added...setting its data source here
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "25%"
ActiveChart.SeriesCollection(1).XValues = Worksheets("data").Range(Worksheets("data").Cells(6, i), Worksheets("data").Cells(LastRow, i))
ActiveChart.SeriesCollection(1).Values = Worksheets("data").Range(Worksheets("data").Cells(6, i + 1), Worksheets("data").Cells(LastRow, i + 1))
'now the line chart is added...setting its data source here
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "50%"
ActiveChart.SeriesCollection(2).XValues = Worksheets("data").Range(Worksheets("data").Cells(6, i), Worksheets("data").Cells(LastRow, i))
ActiveChart.SeriesCollection(2).Values = Worksheets("data").Range(Worksheets("data").Cells(6, i + 6), Worksheets("data").Cells(LastRow, i + 6))
'now the line chart is added...setting its data source here
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "25%"
ActiveChart.SeriesCollection(3).XValues = Worksheets("data").Range(Worksheets("data").Cells(6, i), Worksheets("data").Cells(LastRow, i))
ActiveChart.SeriesCollection(3).Values = Worksheets("data").Range(Worksheets("data").Cells(6, i + 11), Worksheets("data").Cells(LastRow, i + 11))
With Worksheets("main").ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "1 month"
End With

End If

i = i + 15

Next i

End Sub

谁能给我点灯?

Thank you so much

Answer 1

一些建议的更改，包括Step 15

Sub graph()
Dim cht As Chart, co As Object
Dim i As Long
Dim LastRow As Long, rngX As Range, rngY As Range
Dim LastColumn As Long, wsMain As Worksheet, wsVol As Worksheet, wsData As Worksheet
Set wsVol = ThisWorkbook.Worksheets("vol")
Set wsData = ThisWorkbook.Worksheets("data")
Set wsMain = ThisWorkbook.Worksheets("Main")

For i = 2 To 500 Step 15 'loop in increments of 15

If wsVol.Cells(2, i) = wsMain.Cells(3, 3) Then
'define data ranges
Set rngX = wsData.Range(wsData.Cells(6, i), wsData.Cells(Rows.Count, i).End(xlUp))
Set rngY = rngX.Offset(0, 1)

ClearWorksheetCharts wsMain 'remove any existing chart(s)
With wsMain.Range("B26:F37")
'add chartobject, setting position and size
Set co = .Worksheet.Shapes.AddChart(xlLine, .Left, .Top, _
.Width, .Height)
End With

Set cht = co.Chart
ClearChartSeries cht 'remove any "auto-added" series (if data was selected when chart was added)

AddSeries cht, "25%", rngX, rngY
AddSeries cht, "50%", rngX, rngY.Offset(0, 5)
AddSeries cht, "25%", rngX, rngY.Offset(0, 10)

cht.HasTitle = True
cht.ChartTitle.Text = "1 month"
With cht.Axes(xlCategory, xlPrimary)'X axis name
.HasTitle = True 
.AxisTitle.Characters.Text = "Time" 
End With
With cht.Axes(xlValue, xlPrimary)
.HasTitle = True 
.AxisTitle.Characters.Text = "Value"
End With
End If
Next i
End Sub
'add a series and name it (factored out from main sub)
Sub AddSeries(cht As Chart, serName As String, serX, serY)
With cht.SeriesCollection.NewSeries
.Name = serName
.XValues = serX
.Values = serY
End With
End Sub
'remove any existing series from a chart
Sub ClearChartSeries(cht As Chart)
Do While cht.SeriesCollection.Count > 0
cht.SeriesCollection(1).Delete
Loop
End Sub
'Remove any chart objects from `ws`
Sub ClearWorksheetCharts(ws As Worksheet)
Do While ws.ChartObjects.Count > 0
ws.ChartObjects(1).Delete
Loop
End Sub