ثابت مانده نتیجه فرمول بعد از ذخیره در اکسل

**M_ExceL** · 2018/07/29, 20:33

نوشته اصلی توسط Elman

با سلام خدمت اساتید و دوستان گرامی
بنده یک فایل اکسلی دارم که میخوام وقتی از لیست قیمت عدد مورد نظر را پیدا کرد بعد از ذخیره از حالت فرمولی خارج شده و یک عدد ثابت تبدیل گردد که در اثر تغییر قیمت ، قیمتهای قبلی تغییر پیدا نکند.

سلام،
جهت راهنمایی بهتر، نمونه فایلتون رو قرار بدید.

**Elman** · 2018/07/30, 13:14

ضمن تقدیر از شما قسمتی از فایل را پیوست میکنملیست قیمت.xlsx لیست قیمت.xlsx

**M_ExceL** · 2018/07/30, 18:50

نوشته اصلی توسط Elman

ضمن تقدیر از شما قسمتی از فایل را پیوست میکنم[ATTACH]16932[/ATTACH][ATTACH]16932[/ATTACH]

سلام
چک کنید :

فایل های پیوست شده

لیست قیمت 1.zip (45.0 کیلو بایت, مشاهدات 12)

**Elman** · 2018/07/31, 11:30

نوشته اصلی توسط M_ExceL

سلام
چک کنید :

ضمن تشکر از لطف شما بابت تنظیم فایل ، اما باز یک مشکلی دوباره بوجود میاد که در این حالت سلولهایی که وارد نشده را به خالی یا صفر تبدیل میکند که در ادامه بخواهی داده وارد کنی چون فرمول نداره باید دستی وارد بکنی ،اگه امکانش باشه یک شرطی را اضافه نمائید در صورت تهی بودن یا صفر بودن سلول ،فرمول جای خودش باشد در غیر اینصورت عدد ثابت بماند . بازهم تشکر میکنم ازجنابعالی

**M_ExceL** · 2018/07/31, 18:04

نوشته اصلی توسط Elman

ضمن تشکر از لطف شما بابت تنظیم فایل ، اما باز یک مشکلی دوباره بوجود میاد که در این حالت سلولهایی که وارد نشده را به خالی یا صفر تبدیل میکند که در ادامه بخواهی داده وارد کنی چون فرمول نداره باید دستی وارد بکنی ،اگه امکانش باشه یک شرطی را اضافه نمائید در صورت تهی بودن یا صفر بودن سلول ،فرمول جای خودش باشد در غیر اینصورت عدد ثابت بماند . بازهم تشکر میکنم ازجنابعالی

سلام، خواهش میکنم
بفرمایید :

فایل های پیوست شده

لیست قیمت 2.zip (45.3 کیلو بایت, مشاهدات 10)

**Elman** · 2018/08/03, 09:20

نوشته اصلی توسط M_ExceL

سلام، خواهش میکنم
بفرمایید :

سلام خیلی ممنون از لطفی که کردید .
با توجه به اینکه فایلم یه کمی بزرگ هست و ردیفی که در g ثابت کردید در فایل اصلی بنده در ستون ac هست و تعداد ردیفها تا 10000 ردیف هست امکانش هست بفرمائید برای این باید چه چیزهایی را عوض کنم با تشکر

**M_ExceL** · 2018/08/03, 10:33

نوشته اصلی توسط Elman

سلام خیلی ممنون از لطفی که کردید .
با توجه به اینکه فایلم یه کمی بزرگ هست و ردیفی که در g ثابت کردید در فایل اصلی بنده در ستون ac هست و تعداد ردیفها تا 10000 ردیف هست امکانش هست بفرمائید برای این باید چه چیزهایی را عوض کنم با تشکر

سلام، خواهش میکنم
کدها رو به این صورت تغییر بدید :

کد:

Sub bazkhani()
On Error Resume Next
For i = 2 To 10000
Range("ac" & i) = _
"=INDEX(Sheet2!$B$7:$J$497,MATCH(D" & i & ",Sheet2!$A$7:$A$497,0),MATCH(e" & i & ",Sheet2!$B$6:$J$6,0))-INDEX(Sheet2!$K$7:$AX$497,MATCH(d" & i & ",Sheet2!$A$7:$A$497,0),MATCH(F" & i & ",Sheet2!$K$6:$AX$6,0))"
Next i
End Sub
Sub savevalue()
On Error Resume Next
For i = 2 To 10000
If Range("ac" & i).Value > 0 Then
Range("ac" & i) = Range("ac" & i).Value
End If
Next i
End Sub

توجه داشته باشید که ستون های فرمول داخل کد نیز اگر جابجا باشند، باید فرمول رو نیز اصلاح کنید.
یا حق.

**Elman** · 2018/08/04, 11:09

[QUOTE=M_ExceL;63550]سلام، خواهش میکنم
کدها رو به این صورت تغییر بدید :

کد:

Sub bazkhani()
On Error Resume Next
For i = 2 To 10000
Range("ac" & i) = _
"=INDEX(Sheet2!$B$7:$J$497,MATCH(D" & i & ",Sheet2!$A$7:$A$497,0),MATCH(e" & i & ",Sheet2!$B$6:$J$6,0))-INDEX(Sheet2!$K$7:$AX$497,MATCH(d" & i & ",Sheet2!$A$7:$A$497,0),MATCH(F" & i & ",Sheet2!$K$6:$AX$6,0))"
Next i
End Sub
Sub savevalue()
On Error Resume Next
For i = 2 To 10000
If Range("ac" & i).Value > 0 Then
Range("ac" & i) = Range("ac" & i).Value
End If
Next i
End Sub

توجه داشته باشید که ستون های فرمول داخل کد نیز اگر جابجا باشند، باید فرمول رو نیز اصلاح کنید.
یا حق.[/QUO
استاد گرامی بی نهایت تشکر میکنم از لطفی که کردید باز دو تا سوال دارم اگر براتون مقدور هست جوابش را عنایت بفرمایید ممنون میشم . میشه بازه 2 تا 10000 را تغییر داد تا زمان ذخیره را کاهش پیدا کند دوم اینکه این امکان وجود داره با کلیک روی ذخیره تا آخرین ردیف از رنجی که در ac عدد دارد را قفل کرد یا نه؟
h

**M_ExceL** · 2018/08/04, 14:19

نوشته اصلی توسط Elman

استاد گرامی بی نهایت تشکر میکنم از لطفی که کردید باز دو تا سوال دارم اگر براتون مقدور هست جوابش را عنایت بفرمایید ممنون میشم . میشه بازه 2 تا 10000 را تغییر داد تا زمان ذخیره را کاهش پیدا کند دوم اینکه این امکان وجود داره با کلیک روی ذخیره تا آخرین ردیف از رنجی که در ac عدد دارد را قفل کرد یا نه؟

سلام، خواهش میکنم
کدهای زیر رو جایگزین کنید، خدمت شما :

کد:

Sub bazkhani()
Application.ScreenUpdating = False
On Error Resume Next
Worksheets("sheet1").Unprotect Password:="123"
Cells.Locked = False
Cells.FormulaHidden = False
For i = 2 To 10000
Range("ac" & i) = _
"=INDEX(Sheet2!$B$7:$J$497,MATCH(D" & i & ",Sheet2!$A$7:$A$497,0),MATCH(e" & i & ",Sheet2!$B$6:$J$6,0))-INDEX(Sheet2!$K$7:$AX$497,MATCH(d" & i & ",Sheet2!$A$7:$A$497,0),MATCH(F" & i & ",Sheet2!$K$6:$AX$6,0))"
Next i
Application.ScreenUpdating = True
End Sub
Sub savevalue()
Application.ScreenUpdating = False
On Error Resume Next
Cells.Locked = False
Cells.FormulaHidden = False
For i = 2 To 10000
If Range("ac" & i).Value > 0 Then
Range("ac" & i) = Range("ac" & i).Value
Range("ac" & i).Locked = True
End If
Next i
Worksheets("sheet1").Protect Password:="123"
Application.ScreenUpdating = True
End Sub

**Elman** · 2018/08/05, 08:52

با سپاس فراوان از شما استاد گرامی
بنده جواب سوال اولی را خیلی خوب دریافت کردم از این بابت هم خیلی ممنون و متشکرم اما فکرمیکنم سوال دومم را به خوبی مطرح نکردم میخواستم قفل کردنش از اول َa1 تا آخرین ردیف از ac که عدد دارد قفل شود و صرفا ستون ac قفل نشود . با تشکر از الطاف بیکران شما .

**M_ExceL** · 2018/08/05, 17:34

نوشته اصلی توسط Elman

با سپاس فراوان از شما استاد گرامی
بنده جواب سوال اولی را خیلی خوب دریافت کردم از این بابت هم خیلی ممنون و متشکرم اما فکرمیکنم سوال دومم را به خوبی مطرح نکردم میخواستم قفل کردنش از اول َa1 تا آخرین ردیف از ac که عدد دارد قفل شود و صرفا ستون ac قفل نشود . با تشکر از الطاف بیکران شما .

سلام،
خواهش میکنم
بفرمایید، کد زیر رو جایگزین کنید :

کد:

Sub bazkhani()
Application.ScreenUpdating = False
On Error Resume Next
Worksheets("sheet1").Unprotect Password:="123"
Cells.Locked = False
Cells.FormulaHidden = False
For i = 2 To 10000
Range("ac" & i) = _
"=INDEX(Sheet2!$B$7:$J$497,MATCH(D" & i & ",Sheet2!$A$7:$A$497,0),MATCH(e" & i & ",Sheet2!$B$6:$J$6,0))-INDEX(Sheet2!$K$7:$AX$497,MATCH(d" & i & ",Sheet2!$A$7:$A$497,0),MATCH(F" & i & ",Sheet2!$K$6:$AX$6,0))"
Next i
Application.ScreenUpdating = True
End Sub
Sub savevalue()
Application.ScreenUpdating = False
On Error Resume Next
Cells.Locked = False
Cells.FormulaHidden = False
For i = 2 To 10000
If Range("ac" & i).Value > 0 Then
Range("ac" & i) = Range("ac" & i).Value
Range("a1" & ":ac" & i).Select
End If
Next i
With Selection
.Locked = True
End With
Range("a1").Select
Worksheets("sheet1").Protect Password:="123"
Application.ScreenUpdating = True
End Sub

در پناه حق.

**Elman** · 2018/08/06, 09:05

[QUOTE=M_ExceL;63629]سلام،
خواهش میکنم
بفرمایید، کد زیر رو جایگزین کنید :

کد:

Sub bazkhani()
Application.ScreenUpdating = False
On Error Resume Next
Worksheets("sheet1").Unprotect Password:="123"
Cells.Locked = False
Cells.FormulaHidden = False
For i = 2 To 10000
Range("ac" & i) = _
"=INDEX(Sheet2!$B$7:$J$497,MATCH(D" & i & ",Sheet2!$A$7:$A$497,0),MATCH(e" & i & ",Sheet2!$B$6:$J$6,0))-INDEX(Sheet2!$K$7:$AX$497,MATCH(d" & i & ",Sheet2!$A$7:$A$497,0),MATCH(F" & i & ",Sheet2!$K$6:$AX$6,0))"
Next i
Application.ScreenUpdating = True
End Sub
Sub savevalue()
Application.ScreenUpdating = False
On Error Resume Next
Cells.Locked = False
Cells.FormulaHidden = False
For i = 2 To 10000
If Range("ac" & i).Value > 0 Then
Range("ac" & i) = Range("ac" & i).Value
Range("a1" & ":ac" & i).Select
End If
Next i
With Selection
.Locked = True
End With
Range("a1").Select
Worksheets("sheet1").Protect Password:="123"
Application.ScreenUpdating = True
End Sub

در پناه حق.[
خیلی عالی از شما استاد گرامی ممنون و متشکرم