VB help! How to copy amounts from one row to another, how to change text?

[heathclif]

Hello all! I am very new to vB and I was hoping you can give me two tips on how to improve my script. Here is my spreadsheet.

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column12

Column13

Column14

Column15

Column16

Column17

Column18

Column19

4.03

Date

Item

Color

Size

Price

Order

Review

Name

Credit

-4.65

Reorder

Piece

Time

Date

Reason

Shipping

Carrier

Gift

4.03

Date

Item

Color

Size

Price

Order

Review

Name

Debit

4.65

Reorder

Piece

Time

Date

Reason

Shipping

Carrier

Gift

546.42

Date

Item

Color

Size

Price

Order

Review

Name

Credit

-600

Reorder

Piece

Time

Date

Reason

Shipping

Carrier

Gift

546.42

Date

Item

Color

Size

Price

Order

Review

Name

Debit

600

Reorder

Piece

Time

Date

Reason

Shipping

Carrier

Gift

10

Date

Item

Color

Size

Price

Order

Review

Name

Credit

-15.32

Reorder

Piece

Time

Date

Reason

Shipping

Carrier

Gift

10

Date

Item

Color

Size

Price

Order

Review

Name

Debit

15.32

Reorder

Piece

Time

Date

Reason

Shipping

Carrier

Gift

<colgroup><col style="width:48pt" span="19" width="64"> </colgroup><tbody>
</tbody>

Do you know how to:

1. Copy the dollar amount, contained in the first column 1 to column 11.

2. For each row, if column 10 is originally CREDIT change to DEBIT….if originally DEBIT change to CREDIT.

Also the actual spreadsheet has over 2,000 rows, so the script would have to be able to loop and maintain data integrity. Any jelp would be greatly appreciated! Thank you!

<colgroup><col></colgroup><tbody>
</tbody>

 

[heathclif]

Also, I see people embed code into the body of the post, how do you do that? Is there a FAQ that has this info?

 

[Rick Rothstein]

See if this macro does what you want...

Sub Test()
  With Range("J1", Cells(Rows.Count, "J").End(xlUp))
    .Offset(, 1).Value = .Offset(, -9).Value
    .Value = Evaluate("IF(" & .Address & "=""Credit"",""Debit"",""Credit"")")
  End With
End Sub

As to your other question, put the word CODE in square brackets before the start of your code and put /CODE in square brackets after the end of your code. Alternately, select all of your code and click the # button in the ribbon at the top of the reply window (it will place the code tags for you automatically).

 

[heathclif]

Hello Rick! Thank you for this. I noticed that you're always helping out other people a lot. Let me try....

 

[heathclif]

How did you do that

That's amazing Rick. Thank you.