Set my range help please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows
Evening,

I have the code installed below but need advice for the correct way to writ something if i may ask.

The part im stuck with is Set my Range, the sheet is called SOLD ITEMS & the range is C2:C35

Thanks

Code:
Private Sub CommandButton1_Click()Dim myData
Dim myStr As String
Dim x As Integer
Dim myRange As Range


Set myRange = Range Sheets("SOLDITEMS")("C2:C35")


myData = myRange.Value


For x = 1 To UBound(myData, 1)
    myStr = myStr & myData(x, 1) & vbTab & myData(x, 2) & vbCrLf
Next x


MsgBox myStr


End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Code:
Set myRange = Sheets("SOLDITEMS").Range("C2:C35")

Please note I used SOLDITEMS as per your code but your description says SOLD ITEMS so change it if it does have a space.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows
Sorry,
You are correct it is SOLD ITEMS
When i press the button i see a run time error 9 out of range.

The below is shown then in yellow.

Code:
myStr = myStr & myData(x, 1) & vbTab & myData(x, 2) & vbCrLf
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Why do you have the 2 in that line?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

:LOL:
You cant ask me questions like that,as i dont know.

Im trying to use the internet & my peanut brain with the help of the group to have a working item.

Ive now corrected my problem,its C2:D35

I believe im now 99.9% done.

The photo is the end result but need to now edit the fileds etc so it looks like the others, see the gaps ?
The quantity is next to the name on the left.


https://drive.google.com/open?id=1i0OYjwoVcG8sMNN1cr2uXbq1bp6t9kIo
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
:LOL:
You cant ask me questions like that,as i dont know.

I can as we can't see your thoughts, the 2nd number in the array when you write it like that refers to the second column in the range.

I don't know what you mean by
now edit the fileds etc so it looks like the others, see the gaps ?

If you have no data in the rows in column D then you will get blanks in the message box. I don't know what you expect to be done with that.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Did you see the image on the link i supplied ?

The numbers on the right has a gap.
The number is actually there but its far to the left as opposed to being exactly below the number above.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Yes I can see your message box but not your sheet (so not really telling me anything of use) as far as I know from what you have posted here so far there are blanks on the sheet.

Btw why are you posting links to an image rather than the actual workbook?
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,635
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Not blanks but the figure is in the wrong place.
The number where the red arrow is pointing should be where the blue arrow is pointing.

I will upload the workbook for you.

 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,135
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top