Archive on row from 13 cells

michellin

New Member
Joined
Oct 4, 2011
Messages
39
Hy guys

I'm working on my project, but i'm stuck. I search all around the web to get somme idea to finish it. But i can't tell google the good way to find it.

I work on a formula, to keep track of my information automatically on another sheet.

I got on my sheet1 13 cell to backup on my sheet 2, but a want them to be on each row from my first value.

So F5 is my first value (from 1 to infinit), so my first save will be on row 1 (f5 value 1), second row 2 (F5 value 2) third row 3 (f5 value 3), but always on row from my cell F5. So IF i jump a number it will jump with me, and if i came back on a vallue already use, it will tell me.

I found that macro, it save it on next row, but is buggy sometimes, the macro restart up and erase.

Sub archivage()
Dim iR, WsA As Worksheet
Set WsA = Sheets("Archives")
On Error GoTo GESTERRL
With WsA
iR = WsA.Range("archiv").Rows.Count
If Flag = False Then iR = iR + 1
.Cells(iR, 1) = [F5]
.Cells(iR, 2) = [D11]
.Cells(iR, 3) = [D12]
.Cells(iR, 4) = [D13]
.Cells(iR, 5) = [D14]
.Cells(iR, 6) = [D15]
.Cells(iR, 7) = [D16]
.Cells(iR, 8) = [D17]
.Cells(iR, 9) = [C20]
.Cells(iR, 10) = [C21]
.Cells(iR, 11) = [C22]
.Cells(iR, 12) = [C23]
.Cells(iR, 13) = [F24]
End With
Flag = True
Exit Sub
GESTERRL:
iR = 2
Resume Next
End Sub


I dont know if you can help me.
If yes i already thank you from your time.
(sorry i'm a french guys)
Michellin
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Maybe
Code:
Sub michellin()
   Dim iR As Long
   
   With Sheets("Archives")
      iR = Range("F5").Value
      If .Cells(iR, 1) <> "" Then
         MsgBox "Number taken"
         Exit Sub
      End If
      .Cells(iR, 1) = [F5]
      .Cells(iR, 2) = [D11]
      .Cells(iR, 3) = [D12]
      .Cells(iR, 4) = [D13]
      .Cells(iR, 5) = [D14]
      .Cells(iR, 6) = [D15]
      .Cells(iR, 7) = [D16]
      .Cells(iR, 8) = [D17]
      .Cells(iR, 9) = [C20]
      .Cells(iR, 10) = [C21]
      .Cells(iR, 11) = [C22]
      .Cells(iR, 12) = [C23]
      .Cells(iR, 13) = [F24]
   End With
End Sub
 

michellin

New Member
Joined
Oct 4, 2011
Messages
39
HELLLLLL YES Fluff

Thanks a lot, it work really great. I was not so far, but you addon of the msgbox it a great idea.
You just save me a month of research and try and error.
You just make my day. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top