Help with automatically reading a column

Aarondo · July 19th, 2010, 04:57 AM

Hi guys I hope everyone is well.

Im hoping that someone will be able to answer my query as I was trying to find the asnwer to it all day yesterday but didnt have any luck. Im basically wanting to write a VBA script for the following:

Everytime the script is run I want it to automatically read the result from column B and put the result in cell A1 eg

(the script is executed) it will read cell B1 and put "6" in A1
(the script is executed) it will read cell B2 and put "7" in A1
(the script is executed) it will read cell B3 and put "8" in A1
(the script is executed) it will read cell B4 and put "9" in A1 etc etc

A B
- 6
- 7
- 8
- 9

I basically need it to go through column B sequentially and automatically every time the script is run. Is this possible and if so what would be the best way to go about doing this?

Thanks in advance
Aaron

xueliu · July 19th, 2010, 05:26 AM

I can not infer what you really encounter and feel puzzed ,if it is just copy the data from column "B" as to plaste them on column “A”,maybe it is just this code below

Sub test()
Dim MinRow, MaxRow, i As Long

MaxRow = Cells(Rows.Count, "B").End(xlUp).Row 'The value of MaxRow reprensents the last nonempty row
MinRow = Cells(MaxRow, "B").End(xlUp).Row 'The value of MaxRow reprensents the first nonempty row

For i = MinRow To MaxRow
Range("A" & i) = Range("B" & i)
Next

End Sub

I hope it helps

Aarondo · July 19th, 2010, 05:40 AM

Hi thanks for your reply.

That seems to work however when the macro is executed it generates the numbers all in one go. I need it to execute once and generate the first number, then execute again and generate the 2nd number, execute a 3rd time and generate the 3rd number etc etc.

Is this possible?

Thanks

xueliu · July 19th, 2010, 05:57 AM

try this one ,you can add a CommandButton in your worksheet and plaste this code .
Private Sub CommandButton1_Click()
Dim B_MaxRow, B_MinRow, i, A_MaxRow As Long

B_MaxRow = Cells(Rows.Count, "B").End(xlUp).Row 'The value of MaxRow reprensents the last nonempty row in row B
B_MinRow = Cells(B_MaxRow, "B").End(xlUp).Row 'The value of MaxRow reprensents the first nonempty row in row B

A_MaxRow = Cells(Rows.Count, "A").End(xlUp).Row 'The value of MaxRow reprensents the last nonempty row in row A

If Len(Range("A" & A_MaxRow)) = 0 Then

Range("A" & B_MinRow) = Range("B" & B_MinRow)
Else
A_MaxRow = A_MaxRow + 1
Range("A" & A_MaxRow) = Range("B" & A_MaxRow)
End If

End Sub

hope it works this time

Aarondo · July 19th, 2010, 06:59 AM

Hi thanks for your help and quick reply.

This seems to be exactly what Im after, however, the cells and columns that I was using were just as an example. The ones Im actually using are

"sheet1" cell "M9" for the output value and
column B in "sheet 2"

I have tried adjusting your code to fit my needs, but Im getting an error message.

Are you able to point me in the correct direction as this is my first time at programming using VBA.

Thanks again

xueliu · July 19th, 2010, 09:03 AM

Sorry,I could not handle properly this time

Shasur · July 23rd, 2010, 12:57 AM

Quote:

Originally Posted by Aarondo

Hi guys I hope everyone is well.

Im hoping that someone will be able to answer my query as I was trying to find the asnwer to it all day yesterday but didnt have any luck. Im basically wanting to write a VBA script for the following:

Everytime the script is run I want it to automatically read the result from column B and put the result in cell A1 eg

(the script is executed) it will read cell B1 and put "6" in A1
(the script is executed) it will read cell B2 and put "7" in A1
(the script is executed) it will read cell B3 and put "8" in A1
(the script is executed) it will read cell B4 and put "9" in A1 etc etc

A B
- 6
- 7
- 8
- 9

I basically need it to go through column B sequentially and automatically every time the script is run. Is this possible and if so what would be the best way to go about doing this?

Thanks in advance
Aaron

Hi Aaron

You will require a static variable. Try the following hint:

Sub Change_A1Val()
Static gRow As Long
gRow = gRow + 1
Range("A1").Value = Range("B" & gRow).Value
End Sub

Cheers
Shasur

bulldurham71 · August 15th, 2010, 07:44 PM

I have a sheet that contains rows of names, the list grows and shrinks. how can i only show the rows with names and hide the lines that have the vlookups but are returning blank?

I figure there will be a variable that counts the names, then a test (probably an if-then) but i can not figure it out.

Shasur · August 16th, 2010, 05:17 AM

Quote:

Originally Posted by bulldurham71

I have a sheet that contains rows of names, the list grows and shrinks. how can i only show the rows with names and hide the lines that have the vlookups but are returning blank?

I figure there will be a variable that counts the names, then a test (probably an if-then) but i can not figure it out.

The easiest way is to have a filter with non-blanks

IF you want to hide the rows you need to add a function to hide the row and call it from an Sheet_Change or Calculate event

Cheers
Shasur