Posts by widgetwonka

Re: Linking checkboxes and radio buttons

Add a control radio button, and write a macro for it's click action that writes 1 or zero to your linked ranges depending on your desired settings.

Something like this (replace the ranges as needs and names as needed):

Private Sub OptionButton1_Click()
Range("O5").Value = "TRUE"
Range("O6").Value = "TRUE"
Range("O7").Value = "FALSE"
Range("O8").Value = "FALSE"
Range("O9").Value = "FALSE"
Range("O10").Value = "FALSE"
Range("O11").Value = "FALSE"
Range("O12").Value = "FALSE"
Range("O13").Value = "FALSE"
End Sub

Re: INDEX Function within IF Formula

This will find if any of your conditions are true:

=IF(OR(ISERROR(VLOOKUP("*AAA*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*BBB*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*CCC*",$A2,1,FALSE))=FALSE)=TRUE,$B$1,"No Match")

Re: Compare Two Columns to Receive a Specific Result

I can think of two ways to do this.

1. use IF(AND(C2="whatever",D2="whatever"),10,something else
you can nest 7 IFs that way

2. Concatenate the field, then use a lookup to return the value you want
so, Cell E2 formula is =C2&D2
and J2 = IF(ISERROR(VLOOKUP(E2,LOOKUP_RANGE,2,FALSE)),"New String",VLOOKUP(E2,LOOKUP_RANGE,2,FALSE))
Where LOOKUP_RANGE has the combinations you want

Re: Profiling Duration in months between 2 dates

What is the fiscal year in this case? There are 23 months between the two dates specified, but only 20 in your formulas.

Re: Find row with closest value

I found one way to do it. It involves sorting the row in descending order, then copying the row and sorting in ascending order.

My Desc row starts in D16. My Asc row starts in E16. My formula to find the row value is:

=MATCH(IF(ABS(G16-INDEX(E16:E33,MATCH(G16,E16:E33,1)))<ABS(G16-INDEX(D16:D33,MATCH(G16,D16:D33,-1))),INDEX(E16:E33,MATCH(G16,E16:E33,1)),INDEX(D16:D33,MATCH(G16,D16:D33,-1))),E16:E33,0)+15

where G16 has your average.

It is an inelegant solution, so maybe someone else can get you closer...

Re: Auto Scaling &amp; auto formating major/minor grid lines

sub test1()
    Dim xmin As Double
    Dim xmaj As Double
    
    xmin = Range("A1").Value
    xmaj = Range("A2").Value
    
    
    
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    With ActiveChart.Axes(xlCategory)
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .MinorUnit = xmin
        .MajorUnit = xmaj
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
End sub

This is getting close to what you want I believe.

Re: Auto Scaling &amp; auto formating major/minor grid lines

Where does this get you:

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Turd1")) Is Nothing Then
With Worksheets("Plan View").ChartObjects("Chart 2").Chart
.Axes(1).MinimumScale = Range("xMin")
.Axes(1).MaximumScale = Range("xMax")
.Axes(2).MinimumScale = Range("yMin")
.Axes(2).MaximumScale = Range("yMax")
.Axes(xlValue).MajorUnit = ["f1"]
  


End With
End If
End Sub

replacing the [f1] obviously

Re: Deny save if cell empty

You will need to put the worksheet name that you want to drive the action in place of Sheet1. You will have to put this in the Worksheet section of the VBE.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)






If Sheets("Sheet1").[B18] <> "" Then
    If Sheets("Sheet1").[F18] = "" Then
Cancel = True
MsgBox "Save disabled.  Please input a reason code"
Else
Cancel = False
End If
End If
End Sub

Re: Multiple users sending data from UserForms to a single sheet

You should be using a database program. You probably could code around this in Excel, but it is going to be more work than it is worth.

Re: too many vlookups in a formula

Take a look: forum.ozgrid.com/index.php?attachment/37445/

I didn't change you vlookups...I just made it so you don't have to update anything. Just add the Rd# sheets and the sheet will auto populate.

The trick here is to use INDIRECT().

Also, you could easily add 6 to all your lookups. Don't do it in the cell, do it via a reference. That is what I did for your formulas in this workbook. I didn't have to, but I figured it would be illustrative..

Re: Auto Scaling &amp; auto formating major/minor grid lines

I am not terribly familiar with the chat object, but it seems your reference to [f1] needs to reference a value like range("F1").

Re: Formula to deduct value from one cell and add to a different one

B3
=IF(AND(Details!A2="Apples",Details!B2="A",Details!D2="ordered"),0,-Details!C2)

E3
=IF(AND(Details!A2="Apples",Details!B2="A",Details!D2="Ordered"),Details!C2,-B3)

You could substitute "apples for whatever you want, like a link a cell that has a dynamic value.

You can also add more tests to the AND().

Re: Return a cell value based on 3 text criterias

maybe you should try the solution I posted to your previous thread:

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

http://www.ozgrid.com/forum/showthread.php?t=152292

Re: VBA - How to make Evaluate INDEX MATCH work with Variables as Criteria?

break it into two parts:

dim str1 as string
dim str2 as string
str1 = worksheetFormula("INDEX(PromoNumber&""2"",Promotions1&Promotions7,0)")


str2 = Application.Index(promotions8,str1,1)


EditPromo.ProdNo2.value = str2

Re: Copying New First/Last names from Roster and Inserting into New Rows in diff shee

application.screenupdating = false
application.screenupdating = true

The 0s and 1s are for determining if a value is in the list. for the delete list, it determines if the value is in the imported data. for the addNew, it determines if the the entry is already in the caseload sheet.

The formatting is in reference to your desire to format the rows afterwards. You can record a macro to do it, and copy down any formulas that you have.

I used the offset function so that when you delete a row, you don't create a reference error in the mechanics sheet.

Re: Dynamic Chart based on drop down selection

I see what you mean after looking at how the data is setup...I would advise changing it to be in a flat database format. It will be tremendously easier to work with.

Here is the solution:
forum.ozgrid.com/index.php?attachment/37421/

It uses INDEX(INDIRECT()) to get what you need.

Re: Checkbox activating sub function

Glad to help. I finally joined this site after years of coming here to get quick code. I am happy to see I can give back a little.

Re: Logic Process

I added a sheet called mechanics that will do the sorting for you. I have written the code to compare the old and delete and then add the new:

Sub DeleteOld()


ThisWorkbook.Activate


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim str As String
Dim str2 As String
Dim arr1 As Variant
Dim r As Long


Set ws1 = Sheets("Mechanics")
Set ws2 = Sheets("Caseload")


Set rng1 = ws1.Range("J2:J2461")




For r = 2461 To 2 Step -1
If Cells(r, "J").Value = 0 Then
str = "A" & Cells(r, "J").Row
ws2.Range(str).EntireRow.Delete


End If
Next r


End Sub


Sub Addnew()




ThisWorkbook.Activate


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim rng1 As Range
Dim str As String
Dim str2 As String
Dim arr1 As Variant
Dim r As Long


Set ws1 = Sheets("Mechanics")
Set ws2 = Sheets("Caseload")
Set ws3 = Sheets("Client_List_Instructions")


For r = 2461 To 2 Step -1
If Cells(r, "B").Value = 0 Then
str = "A" & Cells(r, "B").Row
ws3.Range(str).EntireRow.Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)


End If
Next r


End Sub


Sub compareAndContrast()
Call DeleteOld
Call Addnew
End Sub

So, just run compareAndContrast and it will do the rest.

I ran out of steam for the format...I'll let someone else tackle that

forum.ozgrid.com/index.php?attachment/37420/

I had to zip it...I added an arbitrary number of comparative rows, you can adjust if you want it bigger/smaller...

Re: Checkbox activating sub function

OK, I have changed your code somewhat. The problem was with how you were using the form. While segregating code is a good idea, skipping from the form to the modules, etc and using the same variable names defined several times was causing the form to think that one of your check boxes was always on, and the other was always off, because FEthernet was used for both boxes.

Also, I changed the directory structure so I could use it on my computer. You will need to change it back in your worksheet.

Here is the code:

Sheet12 private sub:

Private Sub BtnCreate_Click()
   Call Generate
   
End Sub

frmstart code:

Private Sub BtnCancel_Click()
   Unload Me
   Cancel = True
   Sheets("Macro").Select
   
End Sub


Private Sub BtnOK_Click()
   If Len(TxtSite.Value) = "" Then Exit Sub
   Site = TxtSite.Value
   


   If OptFastEthernet.Value = True Then MMLType = "FastEthernet"
   
   On Error Resume Next
    MkDir "C:\Users\jbbrockman\Desktop\" & TxtSite.Value


   'start FastEthernet command
   If CkbScriptNodeB.Value = True Then
        Open "C:\Users\jbbrockman\Desktop\" & FrmStart.TxtSite.Value & "\Add_NodeB.txt" For Output As 2
    


        Print #2, "TEST 1"


    Close 2
    
    End If
   
   If CkbScriptRNC.Value = True Then
   
         Open "C:\Users\jbbrockman\Desktop\" & FrmStart.TxtSite.Value & "\Add_RNC.txt" For Output As 3
            Print #3, "TEST 2"


    Close 3
   
   End If
   






   Unload Me
   
   Sheets("Macro").Select
End Sub




Private Sub OptFastEthernet_Click()


   'start enable Fast Ethernet Script
   CkbScriptNodeB.Enabled = True
   CkbScriptRNC.Enabled = True
   'end enable Fast Ethernet Script
   


End Sub






Private Sub UserForm_Initialize()
   
   If MMLType = "FastEthernet" Then OptFastEthernet.Value = True
   
  
      If MMLType = "FastEthernet" Then
      CkbScriptNodeB.Enabled = True
      CkbScriptRNC.Enabled = True


   Else
      CkbScriptNodeB.Enabled = False
      CkbScriptRNC.Enabled = False


      
   End If
   
  
   If FEthernet1 = 1 Then CkbScriptNodeB.Value = True
   If FEthernet2 = 2 Then CkbScriptRNC.Value = True


    
  
   TxtSite.Value = Site
   TxtSite.SetFocus
End Sub

principalmod code:

Public Cancel As Boolean
Public Site, MMLType, MacroWB, NewWorkbook As String
Public FEthernet1 As Integer










Sub Generate()
   MacroWB = ActiveWorkbook.Name




   MMLType = "Create"
   E1Num = 2


   FEthernet1 = 1


CallFrmStart:
   Site = ""
   Cancel = False
   IncorrectSite = False
   FrmStart.Show
   
   If Cancel = True Then
   Unload FrmStart
   Exit Sub
   End If
 
End Sub

FastEthernetmod was redundant at this point, so I deleted it.

forum.ozgrid.com/index.php?attachment/37418/ -- here is the workbook as well. Don't forget to change the directories back.

In summary, when using a button on a form, drive the actions within the button code. Don't reference back to a module that also uses the same variable.

Hope this helped. Sorry if I inadvertently deleted some functionality. You can add it back, just do it in line with the form.

Re: Get a cell value based on multiple criteria

Steps:

1. Make a pivot table of the Source data. Include all the rows you want, just de-select blank in the drop down later.
2. Use GETPIVOTDATA for the rest:

Original formula:
=GETPIVOTDATA("Sum of Product",$A$3,"Company","Astrix"," Service"," Posting ")

Formula that is driven by Customer Name:

=GETPIVOTDATA("Sum of Product",$A$3,"Company",B3," Service"," Posting ")

The GETPIVOTDATA formula can be manipulated a number of ways. So, if you don't want posting, just change "posting" to the service reference: you are done. Go have a beer and tell the boss you worked on it all night.