Snasui Excel Forum

Code: Select all

=IF(ISNA(INDEX(Import!C:C,MATCH(Inventory!B2,B:B,0))),"",INDEX(Import!C:C,MATCH(Inventory!B2,B:B,0)))

Code: Select all

Sheet7.Cells(final, 8) = "=IF(ISNA(INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0))),"""",IF(INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0))=0,"""",INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0))))"

Code: Select all

Private Sub CommandButton1_Click()

Dim i As Integer
Dim final As Integer
For i = 2 To 1000
If Sheet7.Cells(i, 2) = "" Then
final = i
Exit For
End If
Next
Sheets("Inventory").Unprotect Password:="1234"
Sheet7.Cells(final, 1) = "=IF(RC[1]="""","""",COUNTA(R2C[1]:RC[1]))"
Sheet7.Cells(final, 2) = Addproduct.TextBox1
Sheet7.Cells(final, 3) = Addproduct.TextBox2
Sheet7.Cells(final, 4) = Addproduct.TextBox3
Sheet7.Cells(final, 5) = Addproduct.TextBox4
Sheet7.Cells(final, 6) = Addproduct.TextBox5
Sheet7.Cells(final, 7) = "=IF(ISNA(VLOOKUP(RC[-5],Import!C7:C12,6,0)),""0"",(VLOOKUP(RC[-5],Import!C7:C12,6,0)))"
'Sheet7.Cells(final, 8) = "=IF(ISNA(INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0))),"""",INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0)))"
Sheet7.Cells(final, 8) = "=IF(ISNA(INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0))),"""",IF(INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0))=0,"""",INDEX(Import!C[-5],MATCH(Inventory!RC[-6],C[-6],0))))"
Sheet7.Cells(final, 9) = "=IF(ISNA(VLOOKUP(RC[-7],Export!C7:C12,6,0)),""0"",(VLOOKUP(RC[-7],Export!C7:C12,6,0)))"
'Sheet7.Cells(final, 10) = "=IF(ISNA(INDEX(Export!C[-7],MATCH(Inventory!RC[-8],C[-8],0))),"""",INDEX(Export!C[-7],MATCH(Inventory!RC[-8],C[-8],0)))"
Sheet7.Cells(final, 10) = "=IF(ISNA(INDEX(Import!C[-7],MATCH(Inventory!RC[-8],C[-8],0))),"""",IF(INDEX(Import!C[-7],MATCH(Inventory!RC[-8],C[-8],0))=0,"""",INDEX(Import!C[-7],MATCH(Inventory!RC[-8],C[-8],0))))"
Sheet7.Cells(final, 11) = "=SUM(IF(ISERROR(RC[-4]-RC[-2]),0,RC[-4]-RC[-2]))"
    
Addproduct.TextBox1 = ""
Addproduct.TextBox2 = ""
Addproduct.TextBox3 = ""
Addproduct.TextBox4 = ""
Addproduct.TextBox5 = ""
Sheets("Inventory").Protect Password:="1234"
Savedata.Hide
Addproduct.Hide

End Sub

Code: Select all

With Worksheets("Template")
        i = Application.CountIf( _
                .Range("L2:L15"), ">0")
        Set rs = .Range("A2:N" & 1 + i)

Code: Select all

Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim final As Integer
Dim actual As Integer

For i = 17 To 1000
If Sheet6.Cells(i, 2) = "" Then
final = i
Exit For
End If
Next

Sheet6.Cells(final, 2) = UserFormImport.ComboBox1
Sheet6.Cells(final, 3) = UserFormImport.TextBox1
Sheet6.Cells(final, 4) = UserFormImport.TextBox9
Sheet6.Cells(final, 5) = UserFormImport.TextBox10
Sheet6.Cells(final, 6) = UserFormImport.TextBox11
Sheet6.Cells(final, 7) = UserFormImport.TextBox2
'Sheet6.Cells(final, 8) = UserFormImport.TextBox4
'Sheet6.Cells(final, 9) = UserFormImport.TextBox6
Sheet6.Cells(final, 8) = UserFormImport.TextBox7

 

For j = 2 To 1000
If Sheet7.Cells(j, 2) = Sheet6.Cells(final, 2) Then
actual = Sheet7.Cells(j, 7)
final = UserFormImport.TextBox2 + actual
Sheet7.Cells(j, 7) = final
Exit For
End If
Next

UserFormImport.ComboBox1 = ""
UserFormImport.TextBox1 = ""
UserFormImport.TextBox9 = ""
UserFormImport.TextBox10 = ""
UserFormImport.TextBox11 = ""
UserFormImport.TextBox2 = ""
'UserFormImport.TextBox4 = ""
'UserFormImport.TextBox6 = ""
UserFormImport.TextBox7 = ""
UserFormImport.TextBox8 = ""


UserForm15.Hide
UserFormImport.Hide

End Sub

Private Sub CommandButton2_Click()
UserForm15.Hide
End Sub

Code: Select all

'Other code
With Worksheets("Template")
    i = Application.CountIf( _
            .Range("L2:L15"), "*?")
    Set rs = .Range("A2:N" & 1 + i)
'Other code