Frustrating

[ronnierunfas]

Hi,
I'm a complete novice at VBA and have only managed to get to where i am on this project by playing around with trial & error & error & error.
I have got to a situation where data is required:

1. on one row in w/sheet1
2. some of this data is collected from w/s2
3. then formulas in w/s1 create a new value for the data that was originally from w/s2.
4. this was working fine until i updated and added a loop to skip to the 1st empty cell in a column.
5. this is where i can't get it right, the updated data in w/s1 does not transfer to w/s2 now??

any help would be appreciated. if any of the program would help i'll send it.

Thanks

[Bob Phillips]

Post your workbook, we aren't telepathic.

[ronnierunfas]

[VBA]Private Sub CommandButton1_Click()
If CheckBox14.Value = True Then
Worksheets("3mm").Select
ElseIf CheckBox25.Value = True Then
Worksheets("50mm").Select
End If
If CheckBox1.Value = True Then
Range("B2").Select
ElseIf CheckBox45.Value = True Then
Range("B32").Select
End If

Set currentcell = ActiveCell
Set kgm = ActiveCell.Offset(0, -1)
Set met = ActiveCell.Offset(0, 3)
Set wid = ActiveCell.Offset(0, 0)
Set x = ActiveCell.Offset(0, 1)
Set thk = ActiveCell.Offset(0, 2)

Worksheets("template").Select
Set currentcell = Range("a2")
Set req = currentcell.Offset(-1, 1)


Do

If IsEmpty(currentcell) Then
currentcell.Value = kgm
currentcell.Offset(0, 1).Value = req
currentcell.Offset(0, 3).Value = met
currentcell.Offset(0, 8).Value = wid
currentcell.Offset(0, 9).Value = x
currentcell.Offset(0, 10).Value = thk
'currentcell.Offset(1, 0).EntireRow.Insert




End
End If

Set nextcell = currentcell.Offset(1, 0)
Set currentcell = nextcell

Loop




Set bal = currentcell.Offset(0, 4).Value
ActiveCell.Offset(0, 3).Value = bal
NEG = currentcell.Offset(0, 6).Value
If currentcell.Offset(0, 4).Value <= 0 Then
ActiveCell.Offset(0, 3).Value = NEG
End If

'End If


Unload UserForm1
UserForm1.Hide


End Sub
[/VBA]

Sorry......

[Bob Phillips]

Can you post the workbook, not just the code, data is as important.

[ronnierunfas]

Here's the actual workbook as well.....

[Bob Phillips]

Walk us through the process of what you don't, what should happen, and what does happen, it is a bit involved to sit down and figure.

[ronnierunfas]

well basically all the tabs marked "?mm" contains factual data.

1. the userform selects the correct cell for data required
2. data in columns a-d are fixed. e is variable
3. all the data is transferred to w/s "template"
4. w/s "template" columns c-h then calculate from this data.
5. if column e is a negative value then column g value returns to w/s"?mm" else column e value returns.

basically a stock item is 6 metres long.
say the data inputted will require 7 metres of material.
this will leave a balance of -1 metre
this equates to 1 x 6 metre for order. 5 metres of which will return to stock hence the value needs returning to w/s"?mm"

if the data inputted requires only 5 metres of material a balance of +1 metre is returned. this value is then returned to "?mm"

hope this makes sense??

at the moment everything works taking info from "?mm" to "template", but not vice versa. so the stock qty in "?mm" column e isn't altered from the relevant data in either "template" column g or e.

[Bob Phillips]

Sorry mate, I have read it a few times now, but I haven't got a clue what is going on. Hopefully, someone else will have a better insight.

[nst1107]

You're right, it is frustrating. And, like Bob, I only sort of have an idea of what you want. However, I ran your code and found a problem at this line:
[vba]Set kgm = ActiveCell.Offset(0, -1)[/vba]
To fix that, try using an If statement to check that the active cell isn't in the first column of a worksheet. Something like:
[vba]If ActiveCell.Column = 1 Then
...[/vba]
To make things less confusing for you, try tidying up your code a bit and declaring variables at the beginning of the procedure. Here's what the CommandButton1_Click() procedure looks like with a little housekeeping:
[vba]Private Sub CommandButton1_Click()
Dim kgm, met, wid, x, thk, req
Dim currentcell As Range
If CheckBox14.Value = True Then
Worksheets("3mm").Select
ElseIf CheckBox15.Value = True Then Worksheets("5mm").Select
ElseIf CheckBox16.Value = True Then Worksheets("6mm").Select
ElseIf CheckBox17.Value = True Then Worksheets("8mm").Select
ElseIf CheckBox18.Value = True Then Worksheets("10mm").Select
ElseIf CheckBox19.Value = True Then Worksheets("12mm").Select
ElseIf CheckBox20.Value = True Then Worksheets("15mm").Select
ElseIf CheckBox21.Value = True Then Worksheets("20mm").Select
ElseIf CheckBox22.Value = True Then Worksheets("25mm").Select
ElseIf CheckBox23.Value = True Then Worksheets("30mm").Select
ElseIf CheckBox24.Value = True Then Worksheets("40mm").Select
ElseIf CheckBox25.Value = True Then Worksheets("50mm").Select
End If
If CheckBox1.Value = True Then
Range("B2").Select
ElseIf CheckBox2.Value = True Then Range("B3").Select
ElseIf CheckBox3.Value = True Then Range("B4").Select
ElseIf CheckBox4.Value = True Then Range("B5").Select
ElseIf CheckBox5.Value = True Then Range("B6").Select
ElseIf CheckBox6.Value = True Then Range("B7").Select
ElseIf CheckBox7.Value = True Then Range("B8").Select
ElseIf CheckBox8.Value = True Then Range("B9").Select
ElseIf CheckBox10.Value = True Then Range("B10").Select
ElseIf CheckBox11.Value = True Then Range("B11").Select
ElseIf CheckBox12.Value = True Then Range("B12").Select
ElseIf CheckBox13.Value = True Then Range("B13").Select
ElseIf CheckBox26.Value = True Then Range("B14").Select
ElseIf CheckBox27.Value = True Then Range("B15").Select
ElseIf CheckBox28.Value = True Then Range("B16").Select
ElseIf CheckBox29.Value = True Then Range("B17").Select
ElseIf CheckBox31.Value = True Then Range("B18").Select
ElseIf CheckBox32.Value = True Then Range("B19").Select
ElseIf CheckBox33.Value = True Then Range("B20").Select
ElseIf CheckBox34.Value = True Then Range("B21").Select
ElseIf CheckBox35.Value = True Then Range("B22").Select
ElseIf CheckBox36.Value = True Then Range("B23").Select
ElseIf CheckBox37.Value = True Then Range("B24").Select
ElseIf CheckBox38.Value = True Then Range("B25").Select
ElseIf CheckBox39.Value = True Then Range("B26").Select
ElseIf CheckBox40.Value = True Then Range("B27").Select
ElseIf CheckBox41.Value = True Then Range("B28").Select
ElseIf CheckBox42.Value = True Then Range("B29").Select
ElseIf CheckBox43.Value = True Then Range("B30").Select
ElseIf CheckBox44.Value = True Then Range("B31").Select
ElseIf CheckBox45.Value = True Then Range("B32").Select
End If
kgm = ActiveCell.Offset(0, -1)
met = ActiveCell.Offset(0, 3)
wid = ActiveCell.Offset(0, 0)
x = ActiveCell.Offset(0, 1)
thk = ActiveCell.Offset(0, 2)
Worksheets("template").Select
currentcell = Range("a2")
req = currentcell.Offset(-1, 1)
Do
If IsEmpty(currentcell) Then
currentcell.Value = kgm
currentcell.Offset(0, 1).Value = req
currentcell.Offset(0, 3).Value = met
currentcell.Offset(0, 8).Value = wid
currentcell.Offset(0, 9).Value = x
currentcell.Offset(0, 10).Value = thk
'currentcell.Offset(1, 0).EntireRow.Insert
End
End If
currentcell = currentcell.Offset(1, 0)
Loop
ActiveCell.Offset(0, 3).Value = currentcell.Offset(0, 4).Value
If currentcell.Offset(0, 4).Value <= 0 Then ActiveCell.Offset(0, 3).Value = currentcell.Offset(0, 6).Value
Unload UserForm1
End Sub
[/vba]
You could probably comress it even more if you didn't use Select and ActiveCell.

I know that's not a lot of help, but I don't have a lot of time right now to figure out exactly what your code is supposed to be doing. Perhaps a little more detailed explanation?

[mdmackillop]

You could probably comress it even more if you didn't use Select and ActiveCell.

Agreed. Something like


[VBA]Private Sub CommandButton1_Click()
Dim Sh As Worksheet
Dim Rng as string

'Dim your other variables

If CheckBox14.Value = True Then Set Sh = Worksheets("3mm")
ElseIf CheckBox15.Value = True Then Set Sh = Worksheets("5mm")
ElseIf CheckBox16.Value = True Then Set Sh = Worksheets("6mm")
ElseIf CheckBox17.Value = True Then Set Sh = Worksheets("8mm")
ElseIf CheckBox18.Value = True Then Set Sh = Worksheets("10mm")
'etc.

End If

If CheckBox1.Value = True Then Rng = "B2"
ElseIf CheckBox2.Value = True Then Rng = "B3"
ElseIf CheckBox3.Value = True Then Rng = "B4"
ElseIf CheckBox4.Value = True Then Rng = "B5"
ElseIf CheckBox5.Value = True Then Rng = "B6"
'etc.
End If

With Sh.Range(Rng)
kgm = .Offset(0, -1)
met = .Offset(0, 3)
wid = .Offset(0, 0)
x = .Offset(0, 1)
thk = .Offset(0, 2)
End With


With Worksheets("template").Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = kgm
.Offset(0, 1).Value = req
.Offset(0, 3).Value = met
.Offset(0, 8).Value = wid
.Offset(0, 9).Value = x
.Offset(0, 10).Value = thk
End With

bal = currentcell.Offset(0, 4).Value
ActiveCell.Offset(0, 3).Value = bal

NEG = currentcell.Offset(0, 6).Value
If currentcell.Offset(0, 4).Value <= 0 Then

ActiveCell.Offset(0, 3).Value = NEG
End If
Unload UserForm1
UserForm1.Hide

End Sub[/VBA]

[ronnierunfas]

sorry, i'm obviously not explaining the actions well enough.
will have a look through your examples for ideas this morning.
thanks for offering them up.

my thoughts are that its the confusion over trying to use active & current cells, that are conflicting, which means its not selecting the correct cell in the correct worksheet. what is the most efficient way of naming a few different cells as a datum point for differing operations?
no error comes up but the data does not transfer to the relvant sheet.

if it would help for me to try and explain what the program needs to do, i will give it another go???

[ronnierunfas]

i've got everything i need working properly now except the following:
############
the attachment is highlighted to show the area of concern.
############
i hope this makes it easier to understand???
thanks to all who have tried to help.

[Aussiebear]

What attachment?

[ronnierunfas]

Thanks,
please let me know what you think?

[Bob Phillips]

So, is the problem the #DIV/0 that get created when you hit Enter?

If so, what is supposed to happen when you Enter?

[ronnierunfas]

No. If you activate TEMPLATE worksheet, click on Enter Data then Clear Selection.
Tick 100mm selection & 10mm selection (100x10 is material required).
Type 0.037 (this being the total weight of material required) in text box.
Then click enter.
You will see that column D is the information received from tab 10mm column E, which is the current stock level.
Back in TEMPLATE tab column E is balance of D-C.
This shows i am -3.713 metres short, which relates to 1 length of material (which is a constant of 6 metres), as shown in column H.
Column G is now the stock value.
If there is no shortage, column E is now the stock value.
The stock value now needs to be overwritten in column E in tab 10mm (or ticked value) and 100 (or ticked value)
ie
tab 10
row 18 (100 x 10)
column E value now = tab TEMPLATE column E if positive value OR column G if E is a negative value.


Hope this helps. If any of it confuses you, please ask for clarification!

Cheers.

[ronnierunfas]

I think i have found an easier?? way of describing what i need for a solution:
i have an activecell A1 on sheet2 (which i need to refer back to).
this cell.value is tranferred to cell A1 on sheet1.
the formula on sheet1 is A1+A2=A3
the value in sheet1 A3 must now override the original activecell A1 on sheet2.

can i name the activecell on sheet2 so the final command can be very roughly:

A3.value = total
select (sheet2 cellA1)
selected cell = total

PLEASE HELP!!

Thanks.