copy multiple rows based on cell value

Hello All..!!
I hate to say it, but "I'm new to VBA". I've always used Excel and loved it, but my method was always the method of many many mouse clicks.
Lately i've been working with sheets with more than 1500 rows, that refer to more than 300 worksheets.
I'm finding lots of useful information on the internet in forums here and there. My problem is i'm not able to join the various things I’ve learned.
What I need to do is copy multiple rows based on a numerical value from a single column.

Example:
Quantity A B C ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
to this…
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….

The code I’ve been trying to work with inserts blank rows. Where I need it to copy the previous row based on the value in the first column.
Sub AddMultitipleRows2()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
' Based on the value in column b, where Quantity is in the
' example above.
For iRow = LastRow To FirstRow Step -1
With .Cells(iRow, "b")
If IsNumeric(.Value) Then
If .Value > 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
If .Value = 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
End If
End With
Next iRow
End With
End Sub
thank you to anyone that could take the time to help.
Dogbone...


Post your answer or comment

comments powered by Disqus
Hi,

I'm new with macros and am trying to cut rows from sheet2 to a new sheet based on the values in column a. In Column A there are numbers (1-however many...sometimes 5 sometimes 80) and i want each number to be on its own tab (there are five columns of information for each row...not sure if that matters or not). Each number may have multiple rows associated with it so i would like to cut all those rows and paste them into a new sheet.

Can anyone please help??

I have only have basic levels of Access and VBA and hence have spent hours trying to find a solution in VBA to my issue and was hoping someone might be able to shed any light on how this can be achieved or have any sample code.

I have a table that contains rows of items with a quantity in the final field. ie

Item_name, Size, Colour, Quantity
T-Shirt, Large, Grey, 2
T-shirt, Small, Black, 3

I need the rows to be copied the same number of times as the quantity value so that instead of having 1 row with a quantity of 3, it copies this row a further 2 times so that there are 3 rows for each item ie

Item_name, Size, Colour, Quantity
T-shirt, Large, Grey, 2
T-shirt, Large, Grey, 2
T-shirt, Small, Black, 3
T-shirt, Small, Black, 3
T-shirt, Small, Black, 3

Does anyone have any ideas?

Many thanks in advance.

Hi,

I have the following code, working off an archive button (I didn't write it, I was advised).

Basically it looks through all rows on my "Open Quotes" sheet and looks for the status in column D. If the Status is closed it cuts it out and pastes it into another worksheet "Closed Quotes" and deletes the emtpy row in "Open Quotes".

The problem I have is, when it pastes into the closed Quotes sheet,it leaves an empty line between the last quote and the newly pasted quote(s).

Any thoughts ?

Private Sub Button790_Click()
Application.ScreenUpdating = False
Dim rngOrigin As Range, rngDest As Range
Dim i, j As Integer

i = 1: j = 1
Set rngOrigin = Sheets("Open Quotes").Range("D4")
Set rngDest = Sheets("Closed Quotes").Range("A1").Offset(Application.WorksheetF unction.CountA(Sheets("Closed Quotes").Range("A:A")))

Do While rngOrigin.Offset(i, 0).Value ""
If rngOrigin.Offset(i, 0).Value = "Closed" Then
rngOrigin.Offset(i, 0).EntireRow.Copy
Sheets("Closed Quotes").Activate
rngDest.Offset(j, 0).Select
ActiveSheet.Paste
Sheets("Open Quotes").Activate
rngOrigin.Offset(i, 0).EntireRow.Delete xlShiftUp
j = j + 1
i = i - 1
End If
i = i + 1
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Many thanks

MattP

Hi,

Let's assume we have 3 tables:

Order_Category (Order_Category_ID, Order_Type_Name) with 2 records:
1, Minor
2, Major

Order_Type (Order_Category_ID, Order_Type) with 4 records:
1, Book
1, Pencil
2, Car
2, House

Orders (Order_Category_ID, Order_Type, value) with 2 records:
1, NULL, NULL
1, NULL, NULL
2, NULL, NULL

I want to create a Multiple Items form presenting Orders table with two Combo Boxes:
1. A combo box to select Order_Category_ID.
2. A combo box to select Order_Type. When 1 (Minor) is chosen in the first combo box it should show Book and Pencil, when 2 (Major) is chosen it should show Car and House.

Examples in the Internet show how to do it on a 'single row' forms using the RowSource property.
I tried to use a query like:

SELECT Order_Type
FROM Order_Type
INNER JOIN Orders ON Order_Type.Order_Category_ID = Orders.Order_Category_ID
WHERE Order_Category_ID = [comboBoxOrderCategoryID]

but it sets same values for all records in the Multiple Items form and it should return different values in each rows based on value in the first combo box (Order_Category_ID).

Thank you for your help! :)

Best Regards,
Mike

In MsAccess 2007,I want each row color to change based on a column value in the report.

For example: Select Name,Age,Salary from table 1 -creates 3 rows.

Name Age Salary
A 15 1000
B 25 2000
C 35 3000

if Salary column is less than 1001,row should be in red- Row 1

if Salary column is less than 2001,row should be in green -Row2

if salary is less than 3001, row should be orange-Row3 .
How to do conditional color formatting of the entire row in Msaccess2007 based on a column value of each row. Please help

What I need, is to populate a column with incremental number based on 1 column and the previous rows result.

Will be easier to see it.

Let's says I have the following:
ID_NUM RPT_YR CS
1 1989 0
1 1990 0
1 1991 1
1 1992 1
1 1993 1

I need a column that will increase like so:

ID_NUM RPT_YR CS Increment
1 1989 0 0
1 1990 0 0
1 1991 1 1
1 1992 1 2
1 1993 1 3
1 1994 0 0
1 1995 1 1

This is where it increments based on a value existing in the current row, and increases for each row that contains the value, resetting it's self when the current row is 0 or Null.

Since I don't know exactly how to phrase what I'm asking, I haven't had much luck looking for code for this.

I am trying to highlight a row based on a date. I am tryin to use the DATEVALUE on a range field and am getting a data type mismatch. Any ideas on how to restructure my code to get around this? Thanks


For Each objws In objwb.Worksheets
If Right(objws.Name, 6) = "Detail" Then
'MsgBox objWS.Name
'ws.ResetAllPageBreaks
objws.Activate
lastrow = objws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'LastCol = objws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Set myrange = objws.Range(objws.Cells(3, 1), objws.Cells(lastrow, 20))
Set myrange = myrange.Rows("1:" & myrange.Rows.Count)
For Each r In myrange.Rows
If (r.Cells(, 13) = "OPEN") And DateValue(r.Cells(, 6).Value < DateValue(Me.txt_cfodate)) Then
r.Interior.ColorIndex = 3
r.Cells(, 3).NumberFormat = "$#,##0"
r.Cells(, 3).HorizontalAlignment = xlRight
r.Cells(, 4).NumberFormat = "$#,##0"
r.Cells(, 4).HorizontalAlignment = xlRight
r.Cells(, 5).NumberFormat = "$#,##0"
r.Cells(, 5).HorizontalAlignment = xlRight
r.Cells(, 6).NumberFormat = "$#,##0"
r.Cells(, 6).HorizontalAlignment = xlRight
r.Cells(, 7).NumberFormat = "$#,##0"
r.Cells(, 7).HorizontalAlignment = xlRight
r.Cells(, 8).NumberFormat = "$#,##0"
r.Cells(, 8).HorizontalAlignment = xlRight
End If

Next r
i = i + 1

queryname = "A1:T" & lastrow
objws.PageSetup.PrintArea = queryname

End If
Next objws

Can Access 2010 use VBA to color individual rows in a Listbox?
Access 2010 - This has been asked several times, the answer is to use a repeating form. After searching the Internet for a while this code segment came up. I have not tried it yet.
Does anyone have news, comments or updates about changing the font color for an individual row in the listbox based on a value?
- update: I posted this before going to lunch - this code is not even in the ballpark - it must be for some listbox object outside Access
Still can not find any way. Maybe someone has a good 3rd party Access Listbox control to suggest?

	Code:
	With Me.ListView1
For Counter = 1 To Me.ListView1.ListItems.Count
        Set Item = Me.ListView1.ListItems.Item(Counter)
        ' Set the variable to the amount
        Amount = Item.SubItems(4)
If Amount

Hello.

I've been searching through the threads and couldn't find anything related to my problem. Maybe I'm using the wrong search/key words? If someone knows of a thread, direct me to that as I do not want someone to duplicate work.

What I would like to be able to do is update multiple records based on a user input.

Example:
A user will enter a comment into a record, which can apply to other records in the DB. Rather than have that user re-key or copy and paste the comments, I would like them to be able to open a form, select the records that apply, and have those comments copied to the selected record(s).

Sounds simple and I think I have some ideas in my head but was wondering/hoping if someone has already done this and can help me get this off the ground.

Anyone?

Thanks.

Hello.

I have a spreadsheet that i want to import into an ExternalData table, basically it is normalised (i think) but to be sure i want to import cell values based on the values in other cells.

So looking at my spreadsheet there is a cell value (B6) that contains the value "Sample Description:", and at C6 is the SampleID i want to import into the table,

Then at C22 there is "KF Moisture (ASTM D6304 mod) mg/kg" which is what i want to import into the table field "Analyte" and beside that is the result i want to import into "Result" field.

How can i do a quick check to look for the SampleDescription and use the value it is found in rather than hard coding which cell it is in, so that if it is found at C8 it can still import it?

Has anyone done anything similar?

I'm trying to update a table value based on another value in the same table.

Here's the scenario:

In the table I have 3 estimate descriptions: Sizing, Planning and Commitment. Each description is tied to a series of numbers representing estimates across different teams. For example:

Estimate_Type Team 1 Team 2 Team 3
Sizing ................0........ 0......... 0
Planning .............0........ 0......... 0
Commitment....... 0......... 0......... 0


I currently have all 3 estimate types default to all zeroes when a new team is added to the table. The teams then enter their respective values in the Sizing estimate field. Most commonly, their Sizing estimate will end up equaling the same value as their Planning and Commitment estimate....so, I would like the values for the Planning and Commitment estimates to update automatically when the Sizing estimate changes. My first thought was to use dLookup but I wasn't sure how to make this work. Would I have to run VBA code to sync up the 3 estimates?

This would be easy to accomplish in Excel, but I can't seem to think of a solution for Access. In Excel, I could simply place a reference to the cell with the sizing estimate into the cells for Planning and Commitment. Looking for something comparable in Access??

Thanks in advance.

Forgive me if this has been discussed before...my quick search turned up nothing.

What I would like to do is have the value of several controls be filled in based on the value of another (let's say gateway) control. I have done this before, but only when the value of the gateway control did not matter. In this case, I only want the value of the multiple controls that need filling in to be identical to the gateway control under particular circumstances.

Thus far, here is the code I have written on the gateway control for another purpose:


Private Sub Combo20_AfterUpdate()
If Me.Combo20 = 1 Then
DoCmd.GoToControl "combo24"
Else
DoCmd.GoToControl "combo57"
'if yes to any alzheimers in family answer questions about specific family members,
'else jump to next category of questions
End If

End Sub

Can I also add some code to the AfterUpdate event to specify that iff Me.Combo20= 2 then several other combo responses should equal 2?? I tried accomplishing this in a macro a couple weeks back but somehow that managed to lock up my form. I am hoping to accomplish this in code only, and be able to keep the existing code pasted above (or at least a modified version that accomplishes the same task).

Your assistance will be appreciated!
E.

Is it possible (VBA programmatically) to set color automatically on each textbox in a continuous form based on field value? It means the fields / textboxes on the same column will have different colors based on their different values on different rows/records.

I have come across an issue where I need to have conditional Formatting of a Field based on the value of a different Field.

My company produces customized animal food for our clients. When a new Formula is developed, there is a need for Verification of the Formula before it can be put into production. Recently there have been instances where an Unverified Formula was sent to Production and it turned out that it should not have been, and I was asked to prevent this from happening in the future.

To this end, the Products Table (tblProducts) and the Products entry Form (frmProducts) have been modified to include three new Fields:ModifiedBy - Integer (FK to tblPeople for Staff Member ID) DateModified - DateTime FormulaWasVerified - CheckBox (Default=False) Our Access Program has a Form that is used to create Invoices. Since each Invoice can contain multiple products that are being sold, the Form has a Sub-Form that contains the Invoice Details.

An ideal resolution to the issue would have the Invoice Details Form test the validation Field (FormulaWasValidated) and change the color of the Product_ID Field based on whether or not the Formula for the Product has been verified. This would need to be done on a Product by Product basis. There appear to be at least two problems with this approach.When I try to use Conditional Formatting of the Product_ID Field, there is no obvious way to tie the condition to whether a different Field (FormulaWasValidated) has a value of True or False. When I try to use VB to do the test, it works, but when I modify the background color, ALL Product_ID Fields in the Sub-Form are set to the new color, not just the one that has not been verified. Does anyone have any other ideas?

I have reports based on the value chosen in a combo box. The combo box values can be edited added to etc, but if a new option is added there is no report for this. Is there anyway to automatically create reports (based on a existing report) for any new options added.

Also is there anyway to create a link to the new report on the switchboard automatically or do you have to manually create new reports for any new option entered.

Any help would be appreciated.

Thanks

(If anyone requires any more info just ask)

I am trying to populate a footer based on field values in my report. I have set up an on activate event to loop through the controls and examine their value and then populate a continuos string. So far it ain't working any other ideas?

Is it possible to toggle the .visible status of an object in a Report based on the value of one of the fields in the record?

I have a report called rptDrillDown which gives the details of a record after being called by an On Click event from the summary report. So far, so good. It's calling up just fine. However, each record has a check box indicating whether the study has to be implemented. When the box is checked, a whole bunch of fields show up with dates to track the implementation. When the box is unchecked, the fields are invisible.

This works great on my data entry form, but not so much for my report. The crazy thing is I thought it was working yesterday. Today, not so much.

Here's a sample of the code:


	Code:
	 
Private Sub Report_Load()
If Me.[IM] = -1 Then
    Me.Label75.Visible = True
    Else
    Me.Label75.Visible = False
    End If
End Sub

I deleted out all the other objects - none of them work. The behavior I'm getting is that whether IM (the check box) is checked (-1) or not checked (0), none of the fields show up.

I'm guessing either I can't do this in a report, or the answer is actually pretty simple.

Thanks, Rob

Hello,
I've been reading up on selecting records in a list box using VBA but so far I can only find details of selecting a record based on counting how many there are.
Can anyone tell me how to select a record based on its value.
Eg I have txtbox1 which contains ‘=Format(Now(),”WW”) to give me the week number.
I want to look that week number up in LstBox1 and select it using VBA.
Thanks for any help.

I have found numerous errors in my code since posting this. I now know that trying to populate fields in a report record after opening a report is not an available option. I need to recall the syntax for opening a report with a specific record based on a field value in the current record in a form.


I am trying to select one of a group of reports, and populate its fields with values entered into a form, based on a value selected from a combo box in that form. When I run the code, with a button on click, I get an error;
Run-time error '2212'
Microsoft Office Access couldn't print your object.

Here is my code:

Private Sub Command19_Click()
Dim AcctNoStore As String
Dim LnFnStore As String
Dim LocStore As String
Dim PAOStore As Variant
AcctNoStore = Me.[Acctno]
LnFnStore = Me.[LN,FN]
LocStore = Me.[LOC]
PAOStore = Me.[UponAdmDx]

Select Case Me.[QrySeverType]

Case Is = "CHF"
DoCmd.OpenReport "rptClinDocClarRequestCHF"
Reports!rptClinDocClarRequestCHF![Acctno] = AcctNoStore
Reports!rptClinDocClarRequestCHF![LN,FN] = LnFnStore
Reports!rptClinDocClarRequestCHF![LOC] = LocStore

Case Is = "CKD"
DoCmd.OpenReport "rptClinDocClarRequestKidney"
Reports!rptClinDocClarRequestKidney![Acctno] = AcctNoStore
Reports!rptClinDocClarRequestKidney![LN,FN] = LnFnStore
Reports!rptClinDocClarRequestKidney![LOC] = LocStore

Case Is = "HTN"
DoCmd.OpenReport "rptClinDocClarRequestHypert"
Reports!rptClinDocClarRequestHypert![Acctno] = AcctNoStore
Reports!rptClinDocClarRequestHypert![LN,FN] = LnFnStore
Reports!rptClinDocClarRequestHypert![LOC] = LocStore

Case Is = "DM"
DoCmd.OpenReport "rptClinDocClarRequestDiabet"
Reports!rptClinDocClarRequestDiabet![Acctno] = AcctNoStore
Reports!rptClinDocClarRequestDiabet![LN,FN] = LnFnStore
Reports!rptClinDocClarRequestDiabet![LOC] = LocStore

Case Is = "RESP"
DoCmd.OpenReport "rptClinDocClarRequestResp"
Reports!rptClinDocClarRequestResp![Acctno] = AcctNoStore
Reports!rptClinDocClarRequestResp![LN,FN] = LnFnStore
Reports!rptClinDocClarRequestResp![LOC] = LocStore

Case Is = "POA"
DoCmd.OpenReport "rptClinDocClarRequestPOA"
Reports!rptClinDocClarRequestPOA![Acctno] = AcctNoStore
Reports!rptClinDocClarRequestPOA![LN,FN] = LnFnStore
Reports!rptClinDocClarRequestPOA![LOC] = LocStore
Reports!rptClinDocClarRequestPOA![UponAdmDx] = POAStore

End Select



End Sub

When debugging, I'm sent to the DoCmd line related to the Case Is of the choice selected from the form's combo box.
I would greatly appreciate any time attention and recommendations that might lead me to a solution to my problem.
Again, thank you so very much in advance.

I want to set the RowSource of a ComboBox based on what the value is in another ComboBox. Example:

	Code:
	 
Select Case ComboBox1
 
   Case "CodingList"
      ComboBox2.RowSource = QryCodingList
   Case "DataEntryList"
      ComboBox2.RowSource = QryDataEntryList
   Case "UserList"
      ComboBox2.RowSource = QryUserList
 
end Select
 
Basically I want the Lists available in ComboBox2 to change based on the Value in ComboBox1



i need to create a query that concatenates multiple rows based on another fields matching records

example:

record # fld_Book_Title
record 1: The Three Bears
record 2: The Three Bears
record 3: The Three Bears
record 4: Green Eggs and Ham

record # fld_Page_Numbers
record 1: 56
record 2: 101
record 3: 12
record 4: 42

Query Results

record #: Book: fld_Book_Title
record 1: The Three Bears
record 2: Green Eggs and Ham

record #: Pages: fld_Page_Numbers
record 1: 12, 56, 101
record 2: 42

thanks in advance

Hi all,

I had table with following data

Table

f1 f2 f3
1 10 AB
1 11 WW
2 10 DD
2 11 DD

f3 is memo field

I had to retrieve data by grouping records based on f1 value

and i had to display f3 value as "-" if all f3 values for a f1 are distinct.
If all f3 values contains the same value then i should display that value.

I should get output like this

f1 f3
1 -
2 DD


I tried a lot by taking the distinct count of the records and displaying - when count is > 1

but i am not able to succeed.


please any one give the solution.

waiting for your help.


Thanks

Hi all,

Is it possible to name a field (heading) based on the value of another field?

At the moment the code looks someting like this:


	Code:
	Sum(IIf([START_DATE]=
[CurrentMonth],[COST_PER_UNIT],0),0)) AS [ThisMonth Rate]

I would like to name the field based on the value of another field in the same table instead of the static [ThisMonth Rate]

Thanks again
Jules

How can I set a paramter on my query based on the value held in a field ?

I have a field called WeekEnd (as yes/no field) and another field called WeekDay (where Mon -1, Tue = 2, Wed = 3 etc etc etc).

I am trying to set a parameter on the WeekDay filed so that ....

If the value in WeekEnd = 1 then show WeekDay value >=1 (ie. all values)

If the value in WeekEnd = 0 then show WeekDay value


Not finding an answer? Try a Google search.