Posts by Carim

Re: Moving data from one cell to another based on month selected

Hi DezB,

At least the core issue is working fine ...

Regarding the Conditional Formatting ... it appears as a new requirement ...

Could you possibly attach your workbook in your next message ... :smile:

Cheers

Re: Moving data from one cell to another based on month selected

Hi DezB,

The combination of the DataMove() macro along with the Change event macro should do the job ... No ?

Re: Moving data from one cell to another based on month selected

Hi,

You can have the macro triggered automatically ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
Application.Run ("DataMove")
End Sub

HTH

Re: Moving data from one cell to another based on month selected

Hi,

In essence you need to move your data to Column C ..., you could test following :

Sub DataMove()
Range("D8:D10").Cut Destination:=Range("C8")
Range("D14:D21").Cut Destination:=Range("C14")
Range("D26:D30").Cut Destination:=Range("C26")
Range("D34:D39").Cut Destination:=Range("C34")
Range("D44:D50").Cut Destination:=Range("C44")
End Sub

The second question would be do you need a worksheet event macro whenever cell B3 is modified ...?

HTH

Re: Fill Textboxes Dynamically

Hi,

You could test the following :

Dim i As Long 
For i = 1 To 30 
    Me.Controls("TextBox" & i).Value = Sheets("menu_numbers").Range("A" & i +1).Value 
Next i

HTH

Re: Using checkboxes to sort data

Hi,

To be on the safe side, and sure to get your customized solution ... why don't you attach your excel file in your next message ...

HTH

Re: SUMPRODUCT Formula works, include date format

Hi,

Not sure to fully understand your question ...

If your question relates to extracting the date, you can use =INT(yourdate) ...

and if your want o extract the time, you can use =MOD(yourdate,1)

HTH

Re: Locked combobox

Hi Marti,

You could test ...

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ComboBox1.Locked = False
If OptionButton1.Value = False And OptionButton2.Value = False Then ComboBox1.Locked = True
End Sub

HTH

Re: ComboBox with complicated problem

Glad you could fix your problem ...:smile:

Happy New Year to you ...

Excellente Année 2016 :wink:

Re: ComboBox with complicated problem

Hello again,

Following an excellent remark from cytop, to be on the safe side, you should test:

Private Sub ComboBox4_Change() 
    TextBox23.Value = Val(ComboBox4.Value) * Val(TextBox26.Value) 
    TextBox24.Value = Val(TextBox22.Value) + Val(TextBox23.Value) 
End Sub

HTH

Re: ComboBox with complicated problem

Hello Marti,

A second test ...

Private Sub ComboBox4_Change()
    TextBox23.Value = ComboBox4.Value * CLng(TextBox26.Value)
    TextBox24.Value = CLng(TextBox22.Value) + CLng(TextBox23.Value)
End Sub

HTH

Re: ComboBox with complicated problem

Hi Marti,

You could test the following :

Private Sub ComboBox4_Change()
TextBox23.Value = ComboBox4.Value * TextBox26
TextBox24.Value = TextBox22.Value + TextBox23.Value
End Sub

HTH

Re: Setting colors of cell interior in other sheets

Hi,

In the module ThisWorkbook .... Ten_skoroszyt ... (my Polish is very weak ...) :wink:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x As Long
  For Each Sh In ThisWorkbook.Sheets
    If Sh.name <> "LIST" Then
      If Target.Column <> 1 Then
        x = Application.Match(Target, Sheets("LIST").Range("A1:A100"), 0)
        Target.Interior.Color = Sheets("LIST").Cells(x, 1).Interior.Color
      End If
    End If
  Next Sh
End Sub

Hope this will help

:smile:

Re: Running a macro when a shape is created

Hi,

You could take a look at following site

https://www.add-in-express.com/creating-addins-blog/2012/02/21/excel-shapes-events/

HTH

Re: Macro to move all files including Sub-Folders

Hi,

Would recommend to take a look at Ron's site ... http://www.rondebruin.nl/win/s3/win026.htm

HTH

Re: Count column if NOT a duplicate (Excel 2010)

Hi,

In order to count Unique values, you can test following :

=SUMPRODUCT((K7:K3007<>"")/COUNTIF(K7:K3007,K7:K3007&""))

HTH

Re: Sum from match to last row

Glad this could help you solve your problem ...:smile:

The Offset function requires a starting point A1, then the number of rows, and the number of columns ...

In this case, MATCH("a",A1:A20,0)-1 will generate the number of rows ... to reach the starting row ...

Without this issue of matching the starting row ... your formula would be =SUMPRODUCT((A1:A10="a")*(A1:A10="x")*(B1:B10))

Hope this clarifies ...

Happy Holidays ... to you too ... :smile:

Re: Sum from match to last row

An attempt within a single formula ...

=SUMPRODUCT(((OFFSET(A1,MATCH("a",A1:A20,0)-1,0):A10="a")+(OFFSET(A1,MATCH("a",A1:A20,0)-1,0):A10="x"))*(OFFSET(B1,MATCH("a",A1:A20,0)-1,0):B10))

Obviously "a" has to be replaced by a cell containing "a" or "b" or "c" ... since "x" seems to be fixed ...

Does it produce the expected result ?

Re: Sum from match to last row

A basic UDF could like the following :

Function RobySum(init As String, rng As Range)
Dim x As Long
Dim y As Long
Dim i As Long


x = Application.Match(init, rng, 0)
y = Range("A65536").End(xlUp).Row
  For i = x To y
    If Cells(i, 1).Value = init Or Cells(i, 1).Value = "x" Then
      RobySum = RobySum + Cells(i, 2)
    End If
  Next i
End Function

Hopefully, the result is correct ...

Now, for a non-UDF solution ... do you mind if there is a helper column ?

HTH

Re: Sum from match to last row

Hi,

Sorry I did not realize there was one more condition ... the non matching rows ...

Your life would be easier with a UDF ... :smile:

Are you familiar enough with macros ... or would you like an example ...?