Collect value

I have 4 cells: H8 I8 J8 K8
If change H8, I8 J8 K8 cells have variable value.
I want to change values of H8 from 10 to 150 and collect all 4 cells value 
in columns AF AG AH AI.

Please help me with same VBA code. Thanks.
0
Utf
3/27/2010 7:54:01 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
600 Views

Similar Articles

[PageSpeed] 35

This routine will do the data entry for you and capture the results.  To put 
the code into your workbook, open it and press [Alt]+[F11] to open the VB 
Editor.  Use Insert --> Module in it to open a new code module, then copy and 
paste the code into it.  Select the proper worksheet and then run the Macro.

Sub FillAndCapture()
  Dim homeCell As Range
  Dim baseCell As Range
  Dim LC As Integer
  Dim MLC As Integer
  
  Set homeCell = Range("H8")
  For LC = 10 To 150
    homeCell = LC
    Set baseCell = Range("AF" & Rows.Count). _
     End(xlUp).Offset(1, 0)
    For MLC = 0 To 3
      baseCell.Offset(0, MLC) = _
       homeCell.Offset(0, MLC)
    Next
  Next
  Set baseCell = Nothing
  Set homeCell = Nothing
End Sub


Now, if you want to simply capture what you type into H8 when it changes, 
then use the following worksheet code (don't use the code above).  To put 
this to use, open the workbook, select the appropriate sheet and then 
right-click on its name tab and choose [View Code] from the list and copy the 
code and paste it into the code module.  Any time you type an entry into H8, 
it's contents and those of I8, J8 and K8 will be captured into a new row at 
AF, AG, AH and AI.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim baseCell As Range
  Dim LC As Integer

  If Target.Address <> "$H$8" Then
    Exit Sub
  End If
  'change was made in H8
  'prevent reentry while we're
  'doing the work
  Application.EnableEvents = False
  'set up a reference to the next
  'available cell in column AF on the sheet
  Set baseCell = Range("AF" & Rows.Count). _
   End(xlUp).Offset(1, 0)
  For LC = 0 To 3
    baseCell.Offset(0, LC) = _
     Target.Offset(0, LC)
  Next
  Set baseCell = Nothing
  Application.EnableEvents = True
End Sub


"cferoiu" wrote:

> I have 4 cells: H8 I8 J8 K8
> If change H8, I8 J8 K8 cells have variable value.
> I want to change values of H8 from 10 to 150 and collect all 4 cells value 
> in columns AF AG AH AI.
> 
> Please help me with same VBA code. Thanks.
0
Utf
3/27/2010 11:44:01 PM
Reply:

Similar Artilces:

Ignoring 0 values in the series data
How can I get my chart to ignore values which are zero in the series data and not show these? replace zeros with =NA() Manges -- mangesh_yada ----------------------------------------------------------------------- mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1047 View this thread: http://www.excelforum.com/showthread.php?threadid=38003 You can delete the formulas that are producing zero, or replace them with =IF(formula=0,#N/A,formula) Jerry Alison wrote: > How can I get my chart to ignore values which are zero in the series data a...

Counting Unique Values within a Group
I have a report that lists students by grade and the amount of money donated by that student's family. If the parents are divorced, the student's name is listed twice, once for each parent. I want to count only the unique student record in each grade. DCount is giving me the count for the whole school regardless of what group detail i put it in. How do I count only the unique records within each grouping? Thanks in advance. I would attempt to create a totals query that groups by whatever and counts something. Add this totals query to your report's record source...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 $1.00 013-001 -1 $1.00- 013-002 5 $25.00 013-007 3 $120.00 013-007 -3 $120.00- 022-001 12 $17.00 041-009 7 $251.00 041-009 -7 $251.00- 052-061 10 $30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

Sum value between two dates and copy to new cell
Hi Guys, Thanks for all the previous helps and guidance on excel sheet and im looking forward for new assistants. i've various dates in column A and values in column B. These values should sum and copy to column E according to date between column C & D, example as below, A B C D E Total 04/01/2010 12 01/01/2010 28/02/2010 02/04/2010 133 01/03/2010 30/04/2010 05/06/2010 154 01/05/2010 30/06/2010 03/04/2010 112 01/07/2010 31/08/2010 05/05/2010 65 01/09/2010 31/10/2010 06/07/2010 34 01/11/2010 31/12/2010 02/01/2010 26 02/0...

Macro to insert to move values of a cell to next cell in the same row
Hello, I would like to know the Macro code for Moving the Values of a cell to next cell based upon values in a particular cell. Ex:- Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day) Jan-05 10 Feb-05 10 Based upon date in Col A i want move the value in col b and c to the next cell.Basically this for aging the items in the work sheet Hi There, Can anyone pls give the code for this query. Pls help ...

Can you expand/collapse columns based on a reportitem/field values
I have a report that is driven by accounting periods. The columns are 1, 2, 3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible? Thanks in advance Original Stealth, Certainly can. I created the following dataset: SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3 UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr...

How to have one year value added to another cell
I have a cell where I date foramtted fill in the date. How to have on another cell the date + one year automatically filled? So, A1 02-04-06 auto: B1 02-04-07 Bart Excel 2003 =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Bob Umlas Excel MVP "AA Arens" <bartvandongen@gmail.com> wrote in message news:1164983094.802613.212520@l12g2000cwl.googlegroups.com... > I have a cell where I date foramtted fill in the date. > How to have on another cell the date + one year automatically filled? > > So, > > A1 02-04-06 > > auto: > B1 02-04-07 > > > Bart >...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

valid FirstName value
Hello everyone, Anyone know how to search for a valid FirstName in a field? eg.: Himansu Rich Anna Tom .... I don't want junk like this: AA, 0, "Himansu" 'Rich' Any assisatance will be greatly appreciated. Have a nice evening. Thanks, Himansu How about "Gert-Jan"? //Peter "Himansu" <himansu114@hotmail.com> wrote in message news:u3#lXzwZKHA.6028@TK2MSFTNGP04.phx.gbl... > Hello everyone, > > Anyone know how to search for a valid FirstName in a field? > > eg.: > Himansu > Rich ...

adding values of cells
I am trying to use a formula or function that will look at a cells say column A check for a condition, if true, then add the value of another cell that is on the same row to another cell. Yes I know that may be difficult to understand so let me give you an example. A B C D 1 2 dvd 25.23 3 cd 12.25 4 dvd 25.23 5 cd 12.25 6 bat 19.25 ok so what i want to do is look at column A, and look for any cells that have dvd and add the corresponding value from column D to cell B1. So in this case we can see the cell A2 and cell A4 is dvd so I want to add cells D2 and D4 and that...

Originating Values
I have been workign with GP for a while, but am, not really clear on the difference between Originating Price and Price, Originating Cost and Cost, etc. Can anyone clue me in, I haven't been able to find a good explanation. Thanks Chris I believe this has to do with Multicurrency. 'Originating' is the originating currency. If you are not using Multicurrency the Originating field will be the same as the other field. If your making any changes to the windows using Modifier be careful because these two fields are 'stacked' on top of one another. "Chris Hornun...

Copying Specific Values From Sheet
Hi. Is there a way to copy specific rows from 1 sheet to another? For instance I have a workbook containing 4 tabs. These are labeled "raw data", "Sheet A"," Sheet B", and "Sheet C". On the "raw data" sheet there are 3 columns. One of the columns is titled "Project". In there the values are A, B, C or D (and maybe E someday). I would like to say "if the "Project" column contains and "A" copy everything on that row to "sheet A". Basically I want to take all t he information that has a proje...

Sorting Alpha-Numeric Values
I would like to sort data that have aplha numeric values. When I use the sort command, the numbers wind up getting sorted by place values instead of the actual value of the number, kind of like alphabeticaly except with numbers instead of letters. For instance, I would like to these numbers sorted like this: 17A 36B 265F 1492C 1609A Instead, Excel sorts them like this: 1492C 1609A 17A 265F 36B It appears that the suffixed alpha characters alter the way the numbers would normally be sorted. Any ideas how I can sort them more logically/numericaly? Thanks! Larry ...

converting checkboxes to a text value
I have an existing database that employs checkboxes. I'd like to create a report that displays those boxes as "yes/no" text values, so that I can export them to an .rtf format, and have them actually show up. I'm a novice, so any help is greatly appreciated. The purpose of this is to be able to email specific pages of the report. I'm also open to other ideas for making this possible, if I'm going about this the wrong way. Thanks in advance. Charitycase wrote: > I have an existing database that employs checkboxes. I'd like to > create a report that...

Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below: Column A Column B Apples 24 Pears 36 Oranges 8 I want to poplulate a column (for exampel column A on a new sheet), where the values in column A will be repeated as many times as the value in column B Thus the first 24 rows will say Apples, the next 36 rows will Pears, Oranges. I need a formula that recogonizes that when it gets to row 25 that it should no longer need to copy Apples, but then switch to pears. This might sound like an unusual request but if I can grasp a way to do this I can create the table I need and populate the rest...

different colors for x-Value axis
I want to use different colors for X-Values in X-axis of a chart. Is it possible? Example I-1, I-2 black II-1, II-2, II-3 grey III-1, III-2 red IV pink V-1, V-2, V-3 green -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan The best solution is that, Excel brings the formatting of x-axis value from source cell. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: > I want to use different colors for X-Values in X-axis of a chart. Is it > possible? > Example > I-1, I-2 black > II-1, II-2, II-3 grey > III-1, III-2 red > IV pink >...

Conditional Max value
Hi, i have 2 columns and i'm trying to calculate the conditonal maximum from column one. These are the columns : 47 7 44 7 71 7 58 7 214 4 22 4 54 7 1 7 45 7 21 7 and i try to find a formula that gives the maximum in column one, where column 2 has value 7 in this case this would be 58 Marc Marc, Use the following array formula: =MAX(IF(B1:B10=7,A1:A10,FALSE)) Change the range references to match your data. This is an array formula, so you must press CTRL+SHIFT+ENTER rather than just ENTER when you first enter the for...

Error Msg: Parameters values not valid
When I attempt to drag an e-mail message to the outlook bar task folder to create a task I get an error message: "Could not complete operation. One or more parameter values are not valid". The denied funtionality is useful shortcut. Any ideas why I have lost this shortcut to create tasks? Thank you for any suggestions - Dee Can you create new, blank Task items in your Tasks folder? Try opening your Tasks folder and selecting Save Task Order from the Actions menu. -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www....

a2=a1-32 how do i hide -32 until I have a value in a4
the box a2 contains the formula =a1-32. how can i get rid of -32 that is shown in all the boxes the formula applies to a2 down to k2 thanx I think you have columns and rows mixed up. You cannot have A2 "down to" K2 Across...........yes............down..........no. My best guess to enter in A2 =IF(A1="","",A1-32) Copy across to K2 Gord Dibben MS Excel MVP On Thu, 26 Mar 2009 13:13:02 -0700, jackdaw999 <jackdaw999@discussions.microsoft.com> wrote: >the box a2 contains the formula =a1-32. how can i get rid of -32 that is >shown in all the b...

HOW DO I ADD NON-NUMERIC VALUES
HOW DO I ADD NON-NUMERIC VALUES? Without shouting (i.e., using ALL CAPS), please explain what you mean by "add non-numeric values". Addition normally requires that values be numeric. In article <4DB070A2-B74F-44EA-8FDF-A49C20AE691A@microsoft.com>, DUKE <DUKE@discussions.microsoft.com> wrote: > HOW DO I ADD NON-NUMERIC VALUES? Can you give examples of the "non-numeric values"? (btw, we avoid typing in all capitals in the newsgroups.....it indicates shouting) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "DUKE...

Returning one of two values in Microsoft Excel 2003
I am trying to allocate values across various headings in my worksheet. In, say, A2, data is input via drop down box. Depending on the value of this entry, and on entering a value in B2, B2's value is allocated to a cell under a general heading via the formula =IF(OR(A2="value1", A2="value2", A2="value3"), B2, " ") This is an income allocation spreadsheet. My problem comes in my outgoings. Once again data is input via drop down box and depending on these values I have to allocate my outgoings whether the currency value is input in cell B2 o...

Hlookup with Merged cell as look up value
Hopefully someone can help me out on this one. I have a spreadsheet that looks similar to this: 2/15/2010 3/15/2010 . . . Site# INV VALUE INV VALUE 1 34 332 40 440 2 47 250 47 250 3 55 476 36 420 Total 136 1058 123 1100 I am trying to use a Hlookup to return both the Totals for both the INV and Value in a compainon sheet. The date cells are merged over the INV and VAL Colums so my Hlookup fo...

Excel does not display entered values correctly
I am currently running Excel 2000 on WinXP Pro. Excel does not display the correct values in cells when they are typed. The program arbitrarily assigns a decimal value to whatever number is typed. For example, if "1111111111" is entered, it is displayed as "11111.11111" I have gone through cell formatting, detect and repair and other options, but none have worked. The program use to work fine. It started this recently and it won't go away. If it helps, when a number is typed in another program, i.e. notepad, and copied and pasted into Excel the value displ...

Hi Blinking/Flashing Text in cell based upon date value
Hi, I need a project completion sheet where .. the contents (TEXT) of the particular cell is to flash/blink upon some condition TRUE will any one respond pls Thank q, Syed Hi, Just a personal view but if I opened a workbook and it blinked at me I would close and delete it but if you must then have a look here. http://www.cpearson.com/excel/BlinkingText.aspx -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "EXCEL.SYED" wrote: > H...

Asking Excel Solver to use binaries, but selects other values
I am trying to get a linear program solved using solver. The values that Solver needs to enter are contrained as binary, but excel still try to find values that are neither 0 or 1. Stephane, I assume that when you say it is selecting values that are neither 0 or 1, they are values that are numerically close to 0 or 1 (such as 0.99998). If this is the case, you may want to consider the following approach: 1. Have the Solver constrain your decision variables as binary. 2. Have another set of cells which uses the decision variable to produce a result. When the underlying decision variabl...