VBA Excel-将相同的值添加到不同工作表中的列 [英] VBA Excel - Add same value to columns in different sheet
问题描述
只想问一下如何缩短下面的代码?该代码可以正常工作,但是我只想知道是否有一种方法可以缩短它,因为我将在其他四分之一纸(N-Q1,N-Q2,N-Q3,N-Q4,JK-Q1等).请注意,季度表
具有相同的结构或列(列 16
)( STUDENTS_INFO
)>工作表在 20
列中.
Just want to ask if how can I shorten the codes below? The codes work fine, but I just want to know if there is a way to shorten it since I'll be using the codes in N-Q1
sheet in other quarter sheets (N-Q1, N-Q2, N-Q3, N-Q4, JK-Q1, etc.). Note that, the quarter sheets
have the same structure or column (column 16
) to be updated, while the STUDENTS_INFO
sheet is in column 20
.
学生信息表:
Set ws = ActiveWorkbook.Worksheets("STUDENTS_INFO")
lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
For r = 9 To lastRow
If ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then
If ws.Cells(r, 20) = "0" Or ws.Cells(r, 20) = "" Then
ws.Cells(r, 20) = "1"
Debug.Print "STUDENTS: " & ws.Cells(r, 3) & " Verified!"
Else
Debug.Print "STUDENTS: " & ws.Cells(r, 3) & " Already Verified!"
End If
End If
Next r
N-Q1 代码:季度表(N-Q1,N-Q2,N-Q3,N-Q4,JK-Q1等)>
N-Q1 code: Quarter Sheets (N-Q1, N-Q2, N-Q3, N-Q4, JK-Q1, etc.)
grd = ThisWorkbook.Sheets("HOME").Range("K16").value
qrt = CStr(ThisWorkbook.Sheets("HOME").Range("K17").value)
If grd = "Nursery" Then
ws_output = "N" + "-" + qrt
ElseIf grd = "Junior Kinder" Then
ws_output = "JK" + "-" + qrt
ElseIf grd = "Senior Kinder" Then
ws_output = "SK" + "-" + qrt
End If
Set ws = ActiveWorkbook.Worksheets(ws_output)
lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
For s = 9 To lastRow
If ws.Cells(s, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then
If ws.Cells(s, 16) = "0" Or ws.Cells(r, 16) = "" Then
ws.Cells(s, 16) = "1"
Debug.Print "GRADES: " & ws.Cells(s, 3) & " Verified!"
Else
Debug.Print "GRADES: " & ws.Cells(s, 3) & " Already Verified!"
End If
End If
Next s
请注意,此代码仅根据 grd
和 qrt
的值起作用.
Note that, this code will only work depends on the value of grd
and qrt
.
我正在考虑使用以下代码作为开始.
I'm thinking of using the code below as a start.
For Each ws In Sheets(Array("STUDENTS_INFO", "N-Q1", "N-Q2", "N-Q3", "N-Q4", "N-D", _
"JK-Q1", "JK-Q2", "JK-Q3", "JK-Q4", "JK-D", "SK-Q1", "SK-Q2", "SK-Q3", "SK-Q4", "SK-D"))
With ws.Cells(8, 3).CurrentRegion
.AutoFilter 2, LRN
ws.AutoFilterMode = False
End With
Next ws
推荐答案
请参阅下面的代码,了解我提出的解决方案.谢谢!
Please see the code below for the solution that I came up. Thank you!
For Each ws In Sheets(Array("N-Q1", "N-Q2", "N-Q3", "N-Q4", "N-D", _
"JK-Q1", "JK-Q2", "JK-Q3", "JK-Q4", "JK-D", "SK-Q1", "SK-Q2", "SK-Q3", "SK-Q4", "SK-D"))
lastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
For r = 9 To lastRow
If ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) Then
If ws.Cells(r, 16) = "0" Or ws.Cells(r, 16) = "" Then
ws.Cells(r, 16) = "1"
Debug.Print "STUDENTS: " & ws.Cells(r, 3) & " Verified!"
Else
Debug.Print "STUDENTS: " & ws.Cells(r, 3) & " Already Verified!"
End If
End If
Next r
Next ws
这篇关于VBA Excel-将相同的值添加到不同工作表中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!