VBA for Excel Range - Inputting Info into Specific Columns

[davidmo]

Hey everyone:
I am trying to create a command button assigned to a macro.

I would like the macro to copy the selected rows (range) and copy. Then within the selection change the content for specific columns. In one column I want to change the text from "Rebilled" to "Paid" and in another column multiply the value by -1. After theses change I want to insert the rows I originally copied.

I know how to do the copying and inserting of selected rows, it is the change of the information that I don't know how to do.

I thought of doing a For Next Function but don't know how to specify the cell(s) within the range to change.

Thanks.

DMO

 

[SkipVought]

DMO,

Please remember, we can't see your data OR what you are thinking.

So you have one or more rows selected.

You want to COPY the selection.

THEN WHAT? It has to be PASTED somewhere. WHERE?

When it get pasted, is THAT when the values in the column change?

What are you trying to accomplish with this macro, and don't say that you're trying to copy 'n' paste & change some values. What's the PURPOSE?


Skip,
[sub]
[red][/red]
[/sub]

 

[davidmo]

Skip,
This is for an offline financial spreadsheet to track items that were rebilled to customers who took unauthorized credits.

We need to have the spreadsheet show the originally credit taken on one row and then any payments that apply against it on another row.

The payment row has to have the same information as the credit row for certain fields(customer name, document number, account number) while others need to change (status, amount, financial timeperiod).

The status will always change to "Paid", the new amount will always be the original credit amount multiplied by -1 and the financial timeperiod is always the month the repayment hit our checking account.

I thought to copy the originally credit rows, make the necessary changes to the appropriate fields to make it a payment row and then underneath these rows insert the originally credit rows.

Thanks.
DMO

 

[SkipVought]

Here's the approch I would take.

Assumtion: Table is in column A
Based on the ActiveCell...

dim lNextRow as long, iCol as integer
lNextRow = Cells(Cells.count, 1).end(xlup)+1
for iCol = 1 to ActiveCell.CurrentRegion.Columns.Count
   Select Case iCol
      Case 1  'status column  CHANGE VALUE AS REQUIRED
         cells(lNextRow, iCol).value = "Paid"
      Case 2  'amount column  CHANGE VALUE AS REQUIRED
         cells(lNextRow, iCol).value = cells(Activecell.row, iCol).value * -1
      Case else
         cells(lNextRow, iCol).value = cells(Activecell.row, iCol).value
   End Select
next

Is there a transaction date that should be TODAY's date? That could be another case.

Skip,
[sub]
[red][/red]
[/sub]

 

[davidmo]

Skip:
Thanks for the info and the help.

Would i do this after selecting the rows?

What does SELECT CASE do?

DMO

 

[SkipVought]

This is actually based on ONE row.

Select Case is what I often use instead if a series of If...Then statements. For testing a single value it's alot clearer to me. For instance, it could have been...

   If iCol = 1  Then 'status column  CHANGE VALUE AS REQUIRED
         cells(lNextRow, iCol).value = "Paid"
   ElseIf iCol = 2  Then 'amount column  CHANGE VALUE AS REQUIRED
         cells(lNextRow, iCol).value = cells(Activecell.row, iCol).value * -1
   Else
         cells(lNextRow, iCol).value = cells(Activecell.row, iCol).value
   End If

Skip,
[sub]
[red][/red]
[/sub]