Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 19th, 2010, 04:57 AM
Registered User
 
Join Date: Jul 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with automatically reading a column

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
 
Old July 19th, 2010, 05:26 AM
Registered User
 
Join Date: Jul 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Last edited by xueliu; July 19th, 2010 at 05:32 AM..
 
Old July 19th, 2010, 05:40 AM
Registered User
 
Join Date: Jul 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 19th, 2010, 05:57 AM
Registered User
 
Join Date: Jul 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 19th, 2010, 06:59 AM
Registered User
 
Join Date: Jul 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 19th, 2010, 09:03 AM
Registered User
 
Join Date: Jul 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry,I could not handle properly this time
 
Old July 23rd, 2010, 12:57 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Quote:
Originally Posted by Aarondo View Post
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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old August 15th, 2010, 07:44 PM
Registered User
 
Join Date: Aug 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default hide/unhide based on a variable

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.
 
Old August 16th, 2010, 05:17 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Quote:
Originally Posted by bulldurham71 View Post
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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Text column to image column jomet SQL Server 2000 6 September 18th, 2009 02:36 AM
Transform Automatically DJAJ XSLT 0 March 10th, 2006 09:38 PM
previous column & next column ct Excel VBA 4 August 19th, 2005 04:50 AM
template column and bound column hidayah ASP.NET 1.x and 2.0 Application Design 1 April 9th, 2005 03:50 PM
Compare two Items of data(in column A and column B ever Excel VBA 6 February 13th, 2004 02:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.