Excel Forum

Code: Select all

Public Sub Upload()
Dim Month As String
Dim PiNo1, PiNo2 As Variant
Dim i As Integer

Month = InputBox("Input Month", "Input Month (Ex. Jan , Feb, Mar)")
Sheets(Month).Activate

For i = 3 To 5
If Len(Cells(i, 2).Value) > 9 Then
PiNo1 = Left(Cells(i, 2).Value, 9)
PiNo2 = Right(Cells(i, 2).Value, 9)
Else
PiNo1 = Cells(i, 2).Value
End If

Cells(i, 3).Value = Application.WorksheetFunction.VLookup(PiNo1, Worksheets("PI").Range("B3:C21"), 2, 0)
If Cells(i, 3) <> "" Then
Cells(i, 3).Value = Application.WorksheetFunction.VLookup(PiNo1, Worksheets("PI").Range("B3:C21"), 2, 0) & "" & Application.WorksheetFunction.VLookup(PiNo2, Worksheets("PI").Range("B3:C21"), 2, 0)
End If
Next i

End Sub

Code: Select all

Public Sub Upload()
Dim Month As String
Dim PiNo1, PiNo2 As Variant
Dim i As Integer

Month = InputBox("Input Month", "Input Month (Ex. Jan , Feb, Mar)")
Sheets(Month).Activate

For i = 3 To 5
If Len(Cells(i, 2).Value) > 9 Then
PiNo1 = Left(Cells(i, 2).Value, 9)
PiNo2 = Right(Cells(i, 2).Value, 9)
Cells(i, 3).Value = Application.WorksheetFunction.VLookup(PiNo1, Worksheets("PI").Range("B3:C21"), 2, 0) & "" & Application.WorksheetFunction.VLookup(PiNo2, Worksheets("PI").Range("B3:C21"), 2, 0)
Else
PiNo1 = Cells(i, 2).Value
Cells(i, 3).Value = Application.WorksheetFunction.VLookup(PiNo1, Worksheets("PI").Range("B3:C21"), 2, 0)
End If
Next i

End Sub

Code: Select all

Sub test()

    Dim Month As String
    Dim Ans, PiNo(100), CelVal As Variant
    Dim FirstRow, i, j, k, l As Integer
    
    Month = InputBox("¡ÃسҡÃÍ¡à´×͹", "Input Month (Ex. Jan , Feb, Mar)")
    Sheets(Month).Activate
    
    FirstRow = 2
    i = 1
    
    k = 100
    While k > 0
          k = k - 1
          i = i + 1
          
          CelVal = Cells(FirstRow + i, 2).Value
          
          If CelVal <> 0 Then
          
             ' Reset k while have data row
             k = 100
             j = 0
             
             ' Create each PiNo
             
             j = j + 1
             PiNo(j) = ""
             For l = 1 To Len(CelVal)
                 If Asc(Mid(CelVal, l, 1)) <> 10 And Asc(Mid(CelVal, l, 1)) <> 32 Then
                    PiNo(j) = PiNo(j) & Mid(CelVal, l, 1)
                 Else
                    j = j + 1
                 End If
             Next l
             
             Ans = ""
             
             For l = 1 To j
                  Ans = Ans & WorksheetFunction.VLookup(Val(PiNo(l)), Worksheets("PI").Range("B:C"), 2, 0)
                  If l < j Then Ans = Ans & Chr(10)
             Next l
          
             Cells(FirstRow + i, 3).Value = Ans
          
          
          End If
    Wend
End Sub