VBA - Copying And Pasting Unknown Number Of Values Each Different Number Of Times

May 28, 2014

I am trying to come up with a macro that selects values from one sheet and inserts them into another sheet. The number of values will change each time based on the user's entry, as well as the number of times that each entry should be pasted.

For example:

Entry: X | Y | Z
Number of Times to be Inserted to New Sheet 3 | 2 | 1

Result:
X X X Y Y Z

I have spent a while trying to figure it out, however the best I can come up with is using an array, but I can only get one value from the array to paste multiple times:

(*Note: In my testing, I didn't insert into new sheet or set up the array to handle different values, I was just trying to get the basic idea to work)

Dim A(1, 3) As Variant
A(1, 1) = Range("C3").Value
A(1, 2) = Range("D3").Value
A(1, 3) = Range("E3").Value

[Code].....

View 3 Replies


ADVERTISEMENT

Countif To Find Number Of Unique Values And Number Of Times Repeats Happen

Aug 20, 2013

I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.

I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.

View 9 Replies View Related

Fill Conditional Cell Values Based On Unknown Number Of Rows

Dec 15, 2009

I have a set of data in column R, with an unknown number of rows that looks like this

Days Late
-28
150
3
16
41
.
.
.

and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:

View 4 Replies View Related

Generate Text Value Based On Number Of Times ID Number Previously Appeared?

Aug 3, 2012

Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:

id
event_name
C83-858
sample_1_arm_1

[Code].....

View 3 Replies View Related

Counting The Number Of Times A Multi Digit Number Appears

Nov 22, 2007

I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....

View 9 Replies View Related

Count Number Of Times Specific Intervals Occur Between Relevant Values

May 5, 2008

I will say exactly what i want.

I want to keep statistics of roulette.

More specifically, i want to count how many times a dozen lates to come.

The range from 1-12 is 1st dozen, from 13-24 is the 2nd and 25-36 is the 3rd.

I want to count among these numbers, how many times one of three dozens appear every 1 time, how many times appears every 2 times, .... until 20 times.

For example: I have put in a column 500 numbers from 0 to 36.

The output must be like this:

1st Dozen: Every 1 time: x
Every 2 times: n
Every 20 times: m

The same for 2nd and 3rd dozen.

View 9 Replies View Related

Time (clock) Times A Number= Very High Number?

Apr 2, 2009

I have time start and time end and the difference between the two times. In a third column I have profit and want to calculate the profit per hour. When I divide the profit with the time spent I get a very very high number. If I for instance type in 1 hour of work and profit 100, then I get profit per hour 2400. (profit/hours spent)

On a side note I am trying to get the numbers as 24 hours and not use am or pm and in the spread sheet I get these numbers but in the box at the top where you see the formulas etc. it shows as am and pm. I have uploaded an example.

View 2 Replies View Related

Sum Across Unknown Number Of Worksheets

Sep 20, 2007

The colours are just to mark the ranges

As every month is different the number of sheets adding up to the Red sheets(week total)
will change and the same with the Blue sheet(month end total)

Is there a code I can run for this summing up to be done?

Yellow is where data is entered

Red is where the Yellows range sheets need to add up before it

The Blue is where all the Red range sheets need to add up

View 9 Replies View Related

SUMPRODUCT Use But With Unknown Number Of Rows?

Sep 15, 2012

I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.

I know it is not trivial, but sure it is possible.

57
file:
HIRES-~1
#VALUE!

58
208
1
1

[Code] ...........

View 9 Replies View Related

Combining An Unknown Number Of Cells Into One

Nov 4, 2009

I have the following setup,

Down column A I have a list of jobs, job 1 , job 2 etc
Down column b I have either a blank cell or other data (will be numbers but is irrelevant)
What I want is a formula for a cell that tells me which titles in column A have any data at all in column b. I need it to look like the following : Job 1, Job 2, Job 4
assuming b1 b2 and b4 have any contents and b3 and b5 and onwards are empty

I am seeing that concentate gives me close to what I want but the number of rows in column a and b change constantly and also may be above 30

View 9 Replies View Related

Sort Unknown Number Of Rows

Sep 30, 2009

I am working with 2 workbooks the first one "LH Enrollment" is used to enter information for new students being enrolled in the program. After the information is entered the macro is then copying the information to the second workbook "LH Children Records". The part I'm stuck on is that I now need to sort alphabetically by last name which is entered on Sheet 1, Column B of "LH Children Records". The number of columns will stay the same but the number of rows will change each time a new child is added. Eventually all this information gets copied over to several other workbooks, but I'm stuck trying to sort. I have attached both workbooks

View 5 Replies View Related

Accounting For Unknown Number Of Worksheets

Jun 28, 2006

I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?

Windows("0285 WORKING FILE 0406.XLS").Activate
Sheets(3).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R501C1"
ActiveSheet.Paste
Sheets(2).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R1001C1"
ActiveSheet.Paste

Sometimes I will receive this file and there will be additional tabs. I just never know how many.

I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.

I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.

For each new worksheet selected add 500 rows to the last pasted amount."

View 7 Replies View Related

Average Unknown Number Of Columns

May 25, 2008

I am trying to average a range which continually changes depending on the amount of registries entered in a given week. for example, one week there may be 5 registries which would fill five columns while another week there may be 15 registries, once again filling 15 columns. I would like to find the average of x amount of columns.
My initial approach was to select the first blank cell to the right of the data and then find the last filled cell (which should be the first column of data.
with this range i tried hopelessly to use the average function (did not work):

Sub averagemake()
Dim iLastColumn As Integer
Dim Rng As Range
Sheets("All Data(Values)").Select
Range("A6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
iLastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-iLastColumn]:RC[-1])"
End Sub

View 5 Replies View Related

Combining Worksheets With Unknown Number Of Rows

Dec 16, 2009

I am trying to find a way to combine two worksheets with identical columns and an unknown number of rows. Both sheets use columns A through K.

I want to add a new sheet called "Combined orders", then copy data and headers from Sheet1, paste it to Combined orders, copy data only from Sheet2, and paste it on the row after the last row of data from Sheet1.

Sounds easy but I am easily confused by the unknown number of rows in each data set. Can someone please help?

Also, are there a few lines of code that I can copy and paste into new macros that will make the unknown rows problem easier for me in the future? (i.e., instead of just showing me the code, can you also please explain the concept behind it so I can learn for future reference?)

View 7 Replies View Related

VBA Join Function For Unknown Number Of Arguments

Feb 13, 2012

have a function that takes an unknown number of ranges (worksheet cells) as arguments that can then be joined with the provided delimiter.

It should ignore null values.

I saw that someone posted the following line of code:
Replace(WorksheetFunction.Trim(Join(myArray)), " ", ",")

So I guess my real question is how to pass an unknown number of arguments into a function and put the values in an array.

View 3 Replies View Related

Total A Column With Unknown Number Of Rows

Jan 24, 2007

In the code below, a formula is placed in column F to compute the total of that column. I first find the number of rows and place the formula in the cell below it. I'm dividing the sum by 2 since there are subtotals in the column.

Sub AddColumn()
Dim NumRows As Long
NumRows = Range("A65536").End(xlUp).Row 'get the row count
NumRows = NumRows + 1
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:F308) / 2"
End Sub

The problem with the code is that I don't really know that the last row in the column is F308. I need to replace that part with a variable. It will be something like this (which I know is incorrect):

Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:NumRows) / 2"

How would I use a variable in the SUM function in place of the cell names? Should I even be placing the formula in a cell, or should I compute the sum in the macro and place the value in the cell?

View 2 Replies View Related

Add Data From Unknown Number Of Sheets To Summary Sheet

Aug 1, 2008

How to I add data to an existing summary sheet, in a specific cells, from an unknown number of sheets?

Tickets will be generated with unknown names.

I want the summary sheet to be able pull the ticket numbers into the top row of the summary sheet, to an undefined number of sheets.

As well I would like the summary sheet to pull the job# on each ticket and the hours for each person on each ticket.

View 5 Replies View Related

Copying And Pasting Conditional Formatting With Changing Cell Values

Mar 19, 2013

How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4

If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4

Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get

If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5

How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.

View 6 Replies View Related

Converting Formulas To Values While Copying / Pasting Columns Between Worksheets (VBA)

Feb 6, 2014

I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.

I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.

View 3 Replies View Related

Find Text String And Report Back Data In Unknown Number Of Rows

Apr 26, 2007

For example

Name Address Phone # zip
Danielle 4561
Danielle 9852
Danielle 22
Danielle 69
Joe 895
Joe 28
John 9821
John 1114
John 698

Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?

View 11 Replies View Related

Copy And Pasting One Sheet Under Another For X Number Of Sheets

Sep 27, 2013

Code:
Sub Copy_and_Layout()

Dim ws As Worksheet
ThisWorkbook.Activate
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
If Left(ws.Name, 5) = "Sheet" Then
Range("B2:G" & ws.UsedRange.Rows.count).Copy
Sheets("Rev New").Range("B1").Insert xlDown
End If
Next ws
End Sub

So far I have this as a code but it seems to only paste the last sheet at the top ....

View 3 Replies View Related

Pasting Dates Into Second Worksheet Changes The Serial Number

Aug 28, 2006

I'm having some trouble with copying dates from certain worksheets into new worksheets. When the data is pasted into the new worksheet, the date changes by four years. Somehow, the process of copying and pasting is reducing the serial number by 1462 units (days).

I'm not sure whether this is a bug or a security restriction to prevent copyright breaches. There's a way of getting around it, but I'm curious as to why this is happening. The dates copy and paste correctly into the existing worksheet, but not a new one. An example is attached and the dates are in columns C & D.

View 2 Replies View Related

Card Count: Use Sheet2 To List Each Card Number And The Number Of Times It Appears In The List

May 4, 2009

I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.

View 4 Replies View Related

VBA Copying Unknown Amount Of Rows

Jul 15, 2009

I need to know the function to copy an unknown amount of rows. After a certain row it should copy everything.

So like after row A6 it copys every row below it with data in them then I want to paste it in another worksheet. I dont want to just do

Rows("6:65000").Select
Selection.Copy

If someone could point me to a good site that explains alot of useful VBA commands that would help me alot. Most of my problems come from just not knowing the full function names.

View 10 Replies View Related

Copying And Pasting Current Region To Another Sheet Without Actually Copying?

May 15, 2014

I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:

[Code] ......

The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?

If not, how to copy, then paste?

View 2 Replies View Related

Make Number Formats Into Number Values?

Jul 3, 2013

I have used the formatting option to make numbers that once appeared as 1, 2, 3, 13, etc into four digit numbers, to appear as 3001, 3002, etc.

The problem is, I have other data whose original values are the 3000 formats, and Excel won't combine the two or sort them correctly.

Is there a way I can format it to change the actual value?

View 2 Replies View Related

How Many Times A Number Show Up

Apr 16, 2009

I want a cell to tell me how many times the number 12 is in a group of cells. Is there a formula or something that will.

View 4 Replies View Related

Copy Down X Number Of Times

Oct 4, 2011

I'm trying to modify this line:

Range("A2").AutoFill Destination:=Range("A2:A10"), Type:=xlFillSeries

I trying to copy down a formula a set number of times based on a cell value or a declared value.

View 5 Replies View Related

Count The Number Of Times ...

Apr 24, 2008

Attempting to do a countif as follows

Count the number of times that F2:F65536=Q1 where E2:E65536 also = N2.

So for a given row, the cell in col F needs to equal Q1 and the cell in col E needs to equal N2 for it to count.

View 9 Replies View Related

Run Sub Macro A Certain Number Of Times

Apr 24, 2008

Id like to run a Sub Macro a certain number of times, dependant on the amount of data provided. It could be that I need to run it say 290 times today, but 500 tomorrow.

Is there a bit of code to say, run the macro '500' times?

At the moment, if I set it to run more than it would need to it debugs and gives a compile error, procedure to large.g

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved