Macro help for import sheet

[SaraQuinn]

Hello, I am trying to achieve the following result in the attached sheet. Please advise if anyone can help me with a Macro to convert these values automatically.

• If customer code in column "D" = "UPDS" convert code in column "N" from "1100" to "1101" any other customer code remains "1100".
• If customer code in column "D" = "UPDS" convert codes in column "B" from "4050" to "4049" any other customer code remains "4050".
• If cell in column "D" is blank, cell in column "N" remains blank.

 

[Legacy 143009]

Hi,

This should work for you:

Sub test()
  Dim myRange As Variant, isUPDS As Boolean, i As Long
  myRange = UsedRange
  For i = 1 To UBound(myRange, 1)
    If myRange(i, 4) = "UPDS" Then
      isUPDS = True
      myRange(i, 14) = 1101
    ElseIf isUPDS And myRange(i, 4) = "" Then
      myRange(i, 3) = 4049
    Else
      isUPDS = False
    End If
  Next
  Range("A1").Resize(UBound(myRange, 1), UBound(myRange, 2)).Value = myRange
End Sub

 

[SaraQuinn]

Hi,

This should work for you:

Sub test()
  Dim myRange As Variant, isUPDS As Boolean, i As Long
  myRange = UsedRange
  For i = 1 To UBound(myRange, 1)
    If myRange(i, 4) = "UPDS" Then
      isUPDS = True
      myRange(i, 14) = 1101
    ElseIf isUPDS And myRange(i, 4) = "" Then
      myRange(i, 3) = 4049
    Else
      isUPDS = False
    End If
  Next
  Range("A1").Resize(UBound(myRange, 1), UBound(myRange, 2)).Value = myRange
End Sub

Thank you but it didn't work : ( I got this error

 

[Legacy 143009]

Please try to specify a worksheet name:

Sub test()
  Dim myRange As Variant, isUPDS As Boolean, i As Long
  With Worksheets("yourWorksheet")
  myRange = .UsedRange
  For i = 1 To UBound(myRange, 1)
    If myRange(i, 4) = "UPDS" Then
      isUPDS = True
      myRange(i, 14) = 1101
    ElseIf isUPDS And myRange(i, 4) = "" Then
      myRange(i, 3) = 4049
    ElseIf myRange(i, 4) = "" Then
      myRange(i, 14) = ""
    Else
      isUPDS = False
    End If
  Next
  .Range("A1").Resize(UBound(myRange, 1), UBound(myRange, 2)).Value = myRange
  End With
End Sub

 

[SaraQuinn]

Please try to specify a worksheet name:

Sub test()
  Dim myRange As Variant, isUPDS As Boolean, i As Long
  With Worksheets("yourWorksheet")
  myRange = .UsedRange
  For i = 1 To UBound(myRange, 1)
    If myRange(i, 4) = "UPDS" Then
      isUPDS = True
      myRange(i, 14) = 1101
    ElseIf isUPDS And myRange(i, 4) = "" Then
      myRange(i, 3) = 4049
    ElseIf myRange(i, 4) = "" Then
      myRange(i, 14) = ""
    Else
      isUPDS = False
    End If
  Next
  .Range("A1").Resize(UBound(myRange, 1), UBound(myRange, 2)).Value = myRange
  End With
End Sub

It works perfect now. Thank you very much! I really appreciate your help

 

[Legacy 143009]

Glad it did work! Thanks for the feedback

 

[Legacy 143009]

Please correct this part:

    ElseIf isUPDS And myRange(i, 4) = "" Then
      myRange(i, 3) = 4049
    ElseIf myRange(i, 4) = "" Then
      myRange(i, 14) = ""
    Else

Like this:

    ElseIf isUPDS And myRange(i, 4) = "" Then
      myRange(i, 3) = 4049
      myRange(i, 14) = ""
    ElseIf myRange(i, 4) = "" Then
      myRange(i, 14) = ""
    Else