Random fill 1X2

[Kishan]

I need a VBA or a formula, which can fill random 1X2 in the cells C6:E19

Example....

Book1
	A	B	C	D	E	F	G
1
2
3			1	X	2
4
5		1	1
6		2	1
7		3			2
8		4		X
9		5	1
10		6		X
11		7			2
12		8		X
13		9		X
14		10	1
15		11		X
16		12			2
17		13		X
18		14	1
19
20
21
22
Sheet3

Thank you in advance

Regards,
Kishan

 

[Kishan]

why I do not see the MrExcelHtml shot I posted ? Can anybody help please, also noticed that all my previous threads has disappeared example shots

 

[sandy666]

Welcome to Version 4 of the New MrExcel Message Board!

Way back in 1999, I launched the first MrExcel Message Board using a WWWBoard Perl Script from Matt's Script Archive. Later, we migrated to phpBB and then to vBulletin. The vBulletin board lasted for 12 years. But with vBulletin no longer being supported, as of November 17, 2019, we have...

www.mrexcel.com

 

[Rick Rothstein]

why I do not see the MrExcelHtml shot I posted ? Can anybody help please, also noticed that all my previous threads has disappeared example shots

The forum software is new.... did you use the "Upload image" button at the bottom of the message window to post your picture?

 

[Kishan]

ADVERTISEMENT

The forum software is new.... did you use the "Upload image" button at the bottom of the message window to post your picture?

Thank you Rick I will try it now

 

[Kishan]

well I do not know is it correct? it look to me all complicate

 

[Rick Rothstein]

ADVERTISEMENT

Give this macro a try...

Sub Random1X2()
  Dim R As Long, RndNum As Long
  Randomize
  For R = 6 To 19
    RndNum = 3 * Rnd + 1
    Cells(R, "B").Offset(, RndNum) = Mid("1X2", RndNum, 1)
  Next
End Sub

 

[Kishan]

Hi Rick, thank you for the code it is generating but there are missing some rows it is creating every time different numbers of row 8, 10, 11 an so it must generate 14 please can you take a look
Kind Regards
Kishan

 

[DanteAmor]

How about:

Sub f1x2()
  Dim r As Range, c As Range, n As Long
  Set r = Range("B6:B19")
  r.Offset(, 1).Resize(r.Rows.Count, 3).ClearContents
  Randomize
  For Each c In r
    n = Int(3 * Rnd + 1)
    c.Offset(, n) = Choose(n, "1", "x", "2")
  Next
End Sub

 

[Rick Rothstein]

Hi Rick, thank you for the code it is generating but there are missing some rows it is creating every time different numbers of row 8, 10, 11 an so it must generate 14 please can you take a look
Kind Regards
Kishan

Silly mistake on my part... I forgot an Int function call. Here is the revised code...

Sub Random1X2()
  Dim R As Long, RndNum As Long
  Randomize
  Range("C6:E19").Clear
  For R = 6 To 19
    RndNum = Int(3 * Rnd + 1)
    Cells(R, "B").Offset(, RndNum) = Mid("1X2", RndNum, 1)
  Next
End Sub