时间戳

首页 > 解决方案 > 如何从代码行/特定代码行跳转/跳过

excel - 如何从代码行/特定代码行跳转/跳过

问题描述

当我在我的REVENUE ACCOUNTS部分中生成一个值时，它会正常运行。当我在我的STOCK ACCOUNTS部分中生成一个值时，它最终会与我的REVENUE ACCOUNTS部分混合。当我在我的部分REVENUE ACCOUNTS上输入值时，我如何跳过我的部分。喜欢。_STOCK ACCOUNTSGENERAL CODESTOCK ACCOUNTSGENERAL CODE

Sub ValidateCode2()
Dim igc As String, rev As String, cog As String, dis As String, brandlist As String, prodlist As String
Dim codres As String, revres As String, cogres As String, disres As String
Dim brandresult As String, prodresult As String
Dim i As Long
Dim wb As Workbook: Set wb = ThisWorkbook

sto = wb.Worksheets("Item Groups form").Range("C26").Value
cod = wb.Worksheets("Item Groups form").Range("C18").Value
rev = wb.Worksheets("Item Groups form").Range("C22").Value
dis = wb.Worksheets("Item Groups form").Range("F23").Value
cog = wb.Worksheets("Item Groups form").Range("F22").Value
brandlist = wb.Worksheets("Item Groups form").Range("C19").Value
prodlist = wb.Worksheets("Item Groups form").Range("D19").Value


'''FINISHED GOODS
'STOCK ACCOUNTS
For i = 2 To 13
    If sto = wb.Worksheets("Validation").Range("B" & i).Value Then 
        codres = wb.Worksheets("Validation").Range("D" & i).Value
        revres = wb.Worksheets("Validation").Range("E" & i).Value
        cogres = wb.Worksheets("Validation").Range("F" & i).Value
        disres = wb.Worksheets("Validation").Range("G" & i).Value
Next

'REVENUE ACCOUNTS
For i = 2 To 13
    If rev = wb.Worksheets("Validation").Range("E" & i).Value Then 
        stores = wb.Worksheets("Validation").Range("B" & i).Value
        codres = wb.Worksheets("Validation").Range("D" & i).Value
        cogres = wb.Worksheets("Validation").Range("F" & i).Value
        disres = wb.Worksheets("Validation").Range("G" & i).Value
Next

'''GENERAL CODE
'Brand
For i = 2 To 5000
    If brandlist = wb.Worksheets("Validation").Range("P" & i).Value Then
        brandresult = wb.Worksheets("Validation").Range("Q" & i).Value
Next

'Product
For i = 2 To 5000
    If prodlist = wb.Worksheets("Validation").Range("U" & i).Value Then
        prodresult = wb.Worksheets("Validation").Range("V" & i).Value
Next

wb.Worksheets("Item Groups form").Range("C18").Value = codres + prodresult + brandresult
wb.Worksheets("Item Groups form").Range("C22").Value = revres
wb.Worksheets("Item Groups form").Range("F22").Value = cogres
wb.Worksheets("Item Groups form").Range("F23").Value = disres
wb.Worksheets("Item Groups form").Range("C26").Value = stores

End Sub

标签： excelvba

解决方案

嗨，您要使用的是语句中的GoTo函数。IF我把它塞在循环的第一个块的末尾FOR，但听起来你想要它基于一个条件，但你可以看到GoTo函数是如何工作的，所以你可以相应地调整它。

我还更改了您的 for 循环以消除冗余。

Sub ValidateCode2()
Dim igc As String, rev As String, cog As String, dis As String, brandlist As String, prodlist As String
Dim codres As String, revres As String, cogres As String, disres As String
Dim brandresult As String, prodresult As String
Dim i As Long
Dim wb As Workbook: Set wb = ThisWorkbook

sto = wb.Worksheets("Item Groups form").Range("C26").Value
cod = wb.Worksheets("Item Groups form").Range("C18").Value
rev = wb.Worksheets("Item Groups form").Range("C22").Value
dis = wb.Worksheets("Item Groups form").Range("F23").Value
cog = wb.Worksheets("Item Groups form").Range("F22").Value
brandlist = wb.Worksheets("Item Groups form").Range("C19").Value
prodlist = wb.Worksheets("Item Groups form").Range("D19").Value


'''FINISHED GOODS
'STOCK ACCOUNTS
For i = 2 To 13
    If sto = wb.Worksheets("Validation").Range("B" & i).Value Then 
        codres = wb.Worksheets("Validation").Range("D" & i).Value
        revres = wb.Worksheets("Validation").Range("E" & i).Value
        cogres = wb.Worksheets("Validation").Range("F" & i).Value
        disres = wb.Worksheets("Validation").Range("G" & i).Value
    Else:
        GoTo RevenueAccounts 'You can make this part another if statement to fit your conditions.
Next

RevenueAccounts:
For i = 2 To 13
    If rev = wb.Worksheets("Validation").Range("E" & i).Value Then 
        stores = wb.Worksheets("Validation").Range("B" & i).Value
        codres = wb.Worksheets("Validation").Range("D" & i).Value
        cogres = wb.Worksheets("Validation").Range("F" & i).Value
        disres = wb.Worksheets("Validation").Range("G" & i).Value
Next

'''GENERAL CODE
'Brand
For i = 2 To 5000
    If brandlist = wb.Worksheets("Validation").Range("P" & i).Value Then
        brandresult = wb.Worksheets("Validation").Range("Q" & i).Value
Next

'Product
For i = 2 To 5000
    If prodlist = wb.Worksheets("Validation").Range("U" & i).Value Then
        prodresult = wb.Worksheets("Validation").Range("V" & i).Value
Next

wb.Worksheets("Item Groups form").Range("C18").Value = codres + prodresult + brandresult
wb.Worksheets("Item Groups form").Range("C22").Value = revres
wb.Worksheets("Item Groups form").Range("F22").Value = cogres
wb.Worksheets("Item Groups form").Range("F23").Value = disres
wb.Worksheets("Item Groups form").Range("C26").Value = stores

End Sub

推荐阅读