(VBA) Trying and failing to get q to M values from columm B to columm 12 and beyond

Alex Silva

New Member
Joined
Nov 17, 2017
Messages
3
[h=2]currently i have this[/h]in this 1º button i ask the number of jobs and randomize the time for those jobs between 1 and 10 in columm B
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Private Sub Button1_Click()

Worksheets
("Sheet1").Activate

Range
("A1").Value = "tarefa"
Range
("B1").Value = "tempo"

Dim N As Long
N
= Application.InputBox(Prompt:="N tarefas?(ex. 7 = 6 tarefas)", Type:=1)
If N > Rows.Count Then
N
= Rows.Count
End If

For Z = 2 To N
Cells
(Z, 1) = Z - 1
Cells
(Z, 2) = WorksheetFunction.RandBetween(1, 10)
Next Z
Range
("E1").Value = "total tarefas"
Range
("E2").Value = N - 1
Range
("E7").Value = "ultima celula em A"
Range
("E8").Value = Range("A1").End(xlDown).Row

End Sub</code>in this 2º button i order the values from column B from the greatest to the smallest i ask the number of machines and list the machines i also determine the number of columns that will be needed to be made according to the jobs and the number of machines chosen
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Private Sub Button2_Click()
Worksheets
("Sheet1").Activate

Columns
("A:B").Select
Selection
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom
:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1
:=xlSortNormal

Range
("L1").Value = "máqs a usar?"
Dim N As Long
M
= Application.InputBox(Prompt:="N maq?(ex. 3 = 2 maq)", Type:=1)
If M < 3 Then
M
= 3
End If
If M > Columns.Count Then
M
= Columns.Count
End If

Range
("E4").Value = "total maqs"
Range
("E5").Value = M - 1

For i = 2 To M
Cells
(i, 12) = i - 1
Next i

Range
("E10").Value = "colunas a fazer"
colunasAfazer
= (Cells(2, 5) / Cells(5, 5))
Range
("E11").Value = colunasAfazer
Range
("E13").Value = "arredondamento"
Range
("E14").Value = Round(Cells(11, 5) + 0.44)
arredondado
= Range("E14").Value

For q = 1 To M
w
= q + 1</code>the problem comes here i m trying to get the program to start in line 2 (w=2) column L see if is empty and if is not (because it will have the machines listed there) then go to column M and write the first, second, third and so on bigger number of column B to do only this i don t have a problem
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> Select Case Cells(w, 12)
Case Is = ""
Case Else
Cells
(w, 13) = WorksheetFunction.Large(Columns(2), q)
End Select
Next q
End Sub</code>what i tried and failed to do is get this to be automatic instead of having to do the following that kinda works sometines
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> Select Case Cells(w, 12)
Case Is = ""
Case Else
Cells
(w, 13) = WorksheetFunction.Large(Columns(2), q)
End Select

Select Case Cells(w, 13)
Case Is = ""
Case Else
Cells
(w, 14) = WorksheetFunction.Large(Columns(2), w + M - 1)
End Select
Next q
End Sub</code>^Can anyone help me to make this part automatic to fill the necessary cells with the right q value with any number of jobs and machines chosen ?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top