0

I know my question might have been answered somewhere. However, Either I am not able to find the answer or understand it. Anyways, My question: I am trying to select unique values from different columns and then transpose them to concatenate in a sentence. Everything is working except for the range selection part. I am trying to select rows which are dynamic. And combine them in a sentence and display them in a MsgBox. The Vba code which I have used are First = Range("AA1", Range("AA1").End(xlToRight)) and Second = Range("AA2", Range("AA2").End(xlToRight)). It is giving me a "type mismatch" error. The error is highlighted in yellow

Sub Copy()

Dim MyRange As Range Dim Message As String Dim Second As String Dim WC As String Dim First As String

Set MyRange = Application.InputBox("Select a range from Cell: A2", "Transship Delay", Type:=8) Set r = Range("O1")

MyRange.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=MyRange, CopyToRange:=r

ActiveSheet.Range("O1:O100").RemoveDuplicates Columns:=1, Header:=xlNo

ActiveSheet.Range("P1:P100").RemoveDuplicates Columns:=1, Header:=xlNo

ActiveSheet.Range("W1:W100").RemoveDuplicates Columns:=1, Header:=xlNo

Application.CutCopyMode = False

Range("O1:O50").Copy Range("AA1").PasteSpecial Transpose:=True

Range("W1:W50").Copy Range("AA2").PasteSpecial Transpose:=True

Application.CutCopyMode = False

WC = Range("P1")

First = Range("AA1", Range("AA1").End(xlToRight))

Second = Range("AA2", Range("AA2").End(xlToRight))

MsgBox "Transport damage", vbOKCancel, "[] Units of" & Range("P1") & "were used" & WC & "on" & Second & " have been identified as being subject to a transport damage, and as a result, they are ineligible for reimbursement."

End Sub

I have tried different formulas, but no help. It will be a great help if someone will answer this. Thanks in advance.

Newtocoading
  • 1
  • 2
  • Your question is not clear. "everything but the range selection" is hard to find in your code. Please detail what you expect to happen, what part of your code should make that happen, and what happens instead. Edit your question to do that. Do NOT put clarification into comments. – teylyn Sep 28 '22 at 03:06

1 Answers1

0

I think the problem in your formula is in this code

Set MyRange = Application.InputBox("Select a range from Cell: A2", "Transship Delay", Type:=8)

You could try Setting it Manually first Like if you're looking at the word " Computer "

Set MyRange = "Computer"

If it works then you could set the application to a drop-down type in order to avoid misspelled data.

WIL
  • 117
  • 9