PDA

View Full Version : getting values dynamically from cells A1 t0 A100



sathishesb
12-23-2010, 07:26 AM
Guys help to get values dynamically from cells A1 to A100, i have implemented an algorithm in which i have fetched value from cell A1 processed displayed the result in B1. Now i need to do the same for all cells from A2 to A100 and need to display the result in B2 to B100

Plz help me!! with codes:(

Bob Phillips
12-23-2010, 07:32 AM
Seems too obvious!



Range("A2:A100").Copy Range("B2")

sathishesb
12-23-2010, 07:37 AM
thanks for your reply.

Actually i have read the value from cell A1 and assigned it to n. then processed the value
"n = Worksheets("sheet1").Range("A1")"
now i want to repeat the step with A2(fetch the value from A2 cell assign to n and then to process)

This should be repeated till the loop reaches A100

Plz help me!!!

Bob Phillips
12-23-2010, 08:06 AM
For i = 1 To 100

n = Worksheets("Sheet1").Cells(i, "A").Value
Next i

sathishesb
12-23-2010, 08:18 AM
Thanks a lot it worked for me!!!!

sathishesb
12-23-2010, 08:23 AM
One more doubt xld,

if i am going to paste some 'X' values in from cell A1 to A'x', then i need to find the number 'x'. how do i get it???

Plz guide me !!!

Bob Phillips
12-23-2010, 10:18 AM
I don't know. How is x determined?

sathishesb
12-24-2010, 01:57 AM
depends on the total number of values i am going to paste in a column. for example
A1-A56 or A1-A367 like that

awaiting for ur reply.

shrivallabha
12-24-2010, 02:28 AM
If you are trying to figure out the last row in the column A then use:

Using ActiveSheet
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Unnamed Sheet1
Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Named "MySheet"
Sheets("MySheet").Range("A" & Rows.Count).End(xlUp).Row

Bob Phillips
12-24-2010, 02:35 AM
depends on the total number of values i am going to paste in a column. for example
A1-A56 or A1-A367 like that

awaiting for ur reply.

Unless you tell how x is determined, there is no help we can offer.

sathishesb
12-24-2010, 03:54 AM
Thanks a lot shrivallabha.. it worked for me...

Thanks for ur rely xld... i think from shrivallabha post you would have understand by question.

Thanks a lot for both of you!!!!

sathishesb
12-24-2010, 03:59 AM
Another query, In a loop i am reading values from each cell of Column A. If the cell is blank then it seems it read it as 'NULL' .

My query is: hw to check a variable is NULL..

i tried "if N is NULL" but it throwed error.

Please suggest me an answer....

shrivallabha
12-24-2010, 06:16 AM
It is better to put a new query separately. And you will stand a better chance if you post a sample explaining your requirement completely.

To handle blank case:

With Sheet1
For i = 2 To LastRow
If .Range("A" & i).value = "" Then
'Mycode for blank cell
Else
'Mycode for non-blank cell
End If
Next i
End With

sathishesb
12-29-2010, 12:46 PM
it worked!!!
Thanks a lot for your help guys!!!!!!!!!!!!!!!!!!!!!!

sathishesb
12-29-2010, 12:48 PM
another query,

How to find the lenght of a particular text present in a cell??

for example : 123456789 is there in A1, then i have to get the result as 9

can anyone help this plzzzzzz...............

Bob Phillips
12-29-2010, 02:10 PM
Try

=LEN(A1)

Tinbendr
12-29-2010, 02:13 PM
For instance, A = Len(A1)

sathishesb
12-30-2010, 06:05 AM
thanks a lot!!!!

sathishesb
12-30-2010, 06:17 AM
guys, Is there any way to convert the VBA codes into a protected mode. similar to JAR files in JAVA.

Plzzzz help me guys!!!

Bob Phillips
12-30-2010, 07:43 AM
VBA can be protected, but it is as secure as liquorice.

sathishesb
12-30-2010, 07:54 AM
can you provide me with the procedure to protect the vba codes...

Bob Phillips
12-30-2010, 08:03 AM
You do it in the VBE, Tools>Properties>Protection.

sathishesb
12-30-2010, 08:04 AM
plzz provide me with the procedure to do protect