用另一列的值填充列if语句 [英] Fill in Column with values from another column if statement(s)

问题描述

我目前有一个VBScript，它接收一个Excel文档，并将其重新格式化为更有条理的另一个Excel文档。
此代码还必须查看CATALOG列（B1）的值，并将其放在绘图列（M1）中，如果值的开头以EDASM，EDBSM等开头但是，ED前缀必须在移动时被删除。

例如，目录号EDF12-01114将导致图纸栏中没有任何内容，但是使用EDSM10265，我们需要将SM10265放在图纸栏中（放下ED）。

我已经到目前为止这是， t甚至完成：

 设置objRange = objWorkSheet.Range（M1）。EntireColumn 
 IF 
 objWorkSheet.Range（B1）。Row =EDF *那么可能是正确的？不确定语法
 objRange = Null 
 Else 
 objRange =（B1）'完全是一个可怕的猜测，但我没有任何线索在这里放置
结束如果
  

我看过类似的代码，有循环和whatnot，但没有一个似乎在做我需要的要完成谢谢！

编辑：目前基于BruceWayne的代码。在Excel数据表的Drawing列中仍然没有返回任何内容，但它看起来更接近...

  Sub move_Text（）
 Dim lastRow，nextRow，cel，rng 
 
 lastRow = Cells（Rows.Count，2）.End（xlUp）.Row'由于您的列表B是数据，我们来看看列的最后一行
设置rng =范围（单元格（1，2），单元格（最后一个，2））
 
 nextRow = 1 
 
对于每个cel in rng 
如果Left（cel.Value，3）<> EDF然后
 Cells（nextRow，13）.Value = Mid（cel.Value，3，Len（cel.Value） -  2）
 nextRow = nextRow + 1 
 End If 
下一个
 
 End Sub 
  

另一个编辑！ / strong>
目录列现在是C，而不是B。此外，我有两个标题行，所以第一个目录号位于C3。

再次感谢！我们越来越近了。

以下是Google云端硬盘文件： https://drive.google.com/folderview?id=0B2MeeQ3BKptFYnZfQWpwbTJxMm8&usp=sharing

重要信息要记住

在Google云端硬盘文件中： TestScript.vbs 是所有代码所在的文件。运行脚本时，选择 ExcelImport 。这应该返回 FinalDocument

解决方案

我想这是你正在寻找的：

  Sub move_Text（）
 Dim lastRow，nextRow，cel，rng 
 
'使用数据获取最后一行在列B 
 lastRow = Cells（Rows.Count，B）。End（xlUp）.Row 
'设置从单元格B2开始的范围
设置rng =范围（B2： B& lastRow）
 
'循环遍历范围内的所有单元格，以检查EDF和ED
对于每个cel在r $ $ $ $ $ $ $ $检查字符串是否以EDF开头
如果cel.Value像EDF *那么
'不执行任何
'以下条件是检查字符串是否以ED开头
 ElseIf cel.Value像ED *然后
'删除单元格值的前两个字符，并写入列M 
 cel.Offset（0，11）.Value = Right（cel.Value ，Len（cel.Value） -  2）
'否则条件将在上述两个条件都不满足时执行
'否则条件是基于您的问题中提到的链接，将处理诸如电子BOX之类的单词
 Else 
'write列中的单元格值
 cel.Offset（0，11）.Value = cel.Value 
 End If 
 Next 
 End Sub 
  

编辑：对于VBScirpt
________________________________________________________________________________

  Sub Demo（）
 Dim lastRow，nextRow，cel，rng 
 
 Const xlShiftToRight = -4161 
 Const xlUp = -4162 
 Const xlValues = -4163 
 Const xlWhole = 1 
 Const xlPrevious = 2 
 
使用objWorksheet 
'获取最后一行数据列B 
 lastRow = .Cells（.Rows.Count，C）。End（xlUp）.Row 
 
'设置从单元格B2开始的范围
设置rng = .Range（C2：C& lastRow）
结束
 
'循环遍历范围内的所有单元格，以检查EDF和ED
对于每个cel在下面的
'条件是检查字符串是否以EDF开头
如果InStr（1，cel.Value，EDF，1）= 1然后
'不做任何
'以下条件是检查字符串是否以ED开头
 ElseIf InStr（1，cel.Value，ED，1）= 1然后
'删除单元格值的前两个字符，并写入列M 
 cel.Offset（0，10）.Value = Right（cel.Value，Len（cel.Value） -  2）
'else条件将在上述两个条件都不满足时执行
'else条件是基于您的问题中提到的链接，将处理单词，如电子BOX
 Else 
'在单元格M 
 cel.Offset（0,10）中写入单元格的值.Value = cel.Value 
 End If 
 Next 
 End Sub 
  

I currently have a VBScript that takes in an Excel document and re-formats it into another Excel document that's more organized. This code must also look at the values of the CATALOG column ("B1") and place it in the Drawings column ("M1") ONLY if the beginning of the value starts with "EDASM", "EDBSM" etc., yet the "ED" prefix must be eliminated when it's moved.

For example, Catalog number EDF12-01114 would result in nothing being placed in the drawings column, but with EDSM10265, we would need SM10265 to be placed in the drawings column (drop the "ED").

All I've got so far is this, which isn't even complete:

Set objRange = objWorkSheet.Range("M1").EntireColumn
IF
    objWorkSheet.Range("B1").Row = "EDF*" THEN 'Maybe correct-ish? Not sure about syntax
    objRange = Null
Else 
    objRange = ("B1") 'Totally an awful guess, but I have no clue what to put here
End If

I've seen similar code that has loops and whatnot, but none of them seem to be doing what I need to be done. Thank you!

EDIT: Current code based off of BruceWayne's. Still doesn't return anything in Excel datasheet's Drawing column, but it looks like it's closer...

Sub move_Text()
Dim lastRow, nextRow, cel , rng 

lastRow = Cells(Rows.Count, 2).End(xlUp).Row ' Since your Col. B is the data, let's find that column's last row
Set rng = Range(Cells(1, 2), Cells(lastRow, 2))

nextRow = 1

For Each cel In rng
If Left(cel.Value, 3) <> "EDF" Then
    Cells(nextRow, 13).Value = Mid(cel.Value, 3, Len(cel.Value) - 2)
    nextRow = nextRow + 1
End If
Next

End Sub

Another edit! Catalog column is now "C", not "B". Also, I have two header rows, so the first catalog number is located in "C3".

Thanks again! We're getting closer.

Here's the Google Drive files: https://drive.google.com/folderview?id=0B2MeeQ3BKptFYnZfQWpwbTJxMm8&usp=sharing

IMPORTANT TO REMEMBER

In the Google Drive files: TestScript.vbs is the file where all the code is. When the script is run, select ExcelImport. That should return FinalDocument

解决方案

I guess this is what you are looking for:

Sub move_Text()
    Dim lastRow, nextRow, cel, rng

    'get last row with data in Column B
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    'set your range starting from Cell B2
    Set rng = Range("B2:B" & lastRow)

    'loop through all the cells in the range to check for "EDF" and "ED"
    For Each cel In rng
        'below condition is to check if the string starts with "EDF"
        If cel.Value Like "EDF*" Then
            'do nothing
        'below condition is to check if the string starts with "ED"
        ElseIf cel.Value Like "ED*" Then
            'drop first two characters of cell's value and write in Column M
            cel.Offset(0, 11).Value = Right(cel.Value, Len(cel.Value) - 2)
        'else condition will be executed when none of the above two conditions are satisfied
        'else condition is based on the link mentioned in your question that will handle words like "ELECTRICAL BOX"
        Else
            'write cell's value in Column Q
            cel.Offset(0, 11).Value = cel.Value
        End If
    Next
End Sub

EDIT : For VBScirpt ________________________________________________________________________________

Sub Demo()
    Dim lastRow, nextRow, cel, rng

    Const xlShiftToRight = -4161
    Const xlUp = -4162
    Const xlValues = -4163
    Const xlWhole = 1
    Const xlPrevious = 2

    With objWorksheet
        'get last row with data in Column B
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

        'set your range starting from Cell B2
        Set rng = .Range("C2:C" & lastRow)
    End With

    'loop through all the cells in the range to check for "EDF" and "ED"
    For Each cel In rng
        'below condition is to check if the string starts with "EDF"
        If InStr(1, cel.Value, "EDF", 1) = 1 Then
            'do nothing
        'below condition is to check if the string starts with "ED"
        ElseIf InStr(1, cel.Value, "ED", 1) = 1 Then
            'drop first two characters of cell's value and write in Column M
            cel.Offset(0, 10).Value = Right(cel.Value, Len(cel.Value) - 2)
        'else condition will be executed when none of the above two conditions are satisfied
        'else condition is based on the link mentioned in your question that will handle words like "ELECTRICAL BOX"
        Else
            'write cell's value in Column M
            cel.Offset(0, 10).Value = cel.Value
        End If
    Next
End Sub

这篇关于用另一列的值填充列if语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！