From Cell to multiple cells in Column

[prateekdewangan]

Hello All,

I'm new to Excel VB. Currently , I want to do following -

• Read from a Cell. Something like this "010111" (in Binary). Lets say its in sheet 1, Cell A10.
• Write into multiple cells the same information like this. Lets say in sheet 2. Cell B1 to B6. like , Sheet 2 - B1 -0
  Sheet 2 - B2 -1
  Sheet 2 - B3 -0
  Sheet 2 - B4 -1
  Sheet 2 - B5 -1
  Sheet 2 - B6 -1
• Also, I want to make it generic, I mean I wanted to have a for loop or something like which can do this for dynamic cells. i.e, from sheet 1 - A(i) to sheet 2 - B(j) to B(j+n).
  
  Please help me out? Is this possible to read individual character from the string. I assume that we can take the binary as string & read each character & can then put them one by one into column rows making it dynamic for any row/column.
  
  Thank you,
  Prateek

 

[prateekdewangan]

Hello,


I found an answer, simpler to understand for myself. Anyways thanks for all your suggestion. I couldn't post the reply as was busy developing the tool. Here's small piece of code for you guys.


'Our first Job is to define the range.
'Here I'm selecting D10 to K10. You can check same while debugging the number of 'rows & column.


Dim rng As Range, cel As Range


Set rng = Range("D10", "K10")


'Define the counter variables
Dim i As Integer, j As Integer


For Each cel In rng
If Len(cel) > 0 Then
For i = 1 To Len(cel)
'Extract a single character from the string Cel
'MID( Input_string, Start_position, Number_of_characters )


char = Mid(cel.Value, i, 1)
'Decide what you want to do with the values
If char = 0 Then
'Select the sheet & cell location where you want to Print
Sheet4.Cells(9 + j, 1).Value = char + " Hey Prateek,Its a Zero"
ElseIf char = 1 Then
Select the sheet & cell location where you want to Print
char = 180
Sheet4.Cells(9 + j, 1).Value = char + " Hey Prateek, Its a One"
End If
'Perform this If you want to write this at another location as well
'oFile.WriteLine Sheet4.Cells(9 + j, 1).Value
'Loop the characters & then Increment the Rows in your destination
j = j + 1
'Loop for the next cell
Next i
End If
Next cel

The values in the image are just for representation, Run the code to see actual answer.

Regards ,
Patrick Dew

Have a Nice time with your Excel.