Search results

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub If Target.Value <> "" Or Target.Value <> " " Then Target.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)" End Sub

Aggie81 Try the following: Sub x() Dim LR As Integer LR = Range("A" & Rows.Count).End(xlUp).Row Dim c As Range For Each c In Range(Cells(5, 1), Cells(6, 1)) If c.Value <> "" Then c.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)" Next c End Sub

Once again hard without seeing it Remember that Excel files are simply fancy Zip files Some data like straight data and tables stores really well Other data like Pictures or Logo's don't store well Also avoid Pivot tables where you add data to a Data Model

Do you have a backup ? Can you share the file here ?

I have given you enough assistance in the above to help you Please give it a go yourself first

It is not really clear what you are after but you have a few options Sub Purchase() If Sheets("PURCHASE").Range("D3").Text <> "Some text" Then Exit Sub 'Put the rest of the code here End Sub or Sub Purchase() If Sheets("PURCHASE").Range("D3").Text = "Some text" Then...

Have a read of this: https://superuser.com/questions/1401664/excel-vba-macros-have-disappeared

Paul Did you start from a *.xlsx or *.csv etc file and then add your code? If you didn't change file types to a *.xlsm that is why your code is gone

I'll do it over the weekend and then send you a link to review before publishing

something like: =SUM(1/COUNTIF(OFFSET(A2,0,0,COUNTA(A2:A1048576),1),OFFSET(A2,0,0,COUNTA(A2:A1048576),1))) Ctrl+Shift+Enter

Marc Nice Job ! Would you like that written up as a Blog Post ? .

Good Luck DEBLACKJAKAL

Try the attached

Pazi on your worksheet you can call VisibleSpectrun simply ie: in I11: =VisibleSpectrun(G11) it returns violet ñâåì To use the function in another subroutine or function simply use as an example i rewrote your VisibleSpectrumValue sub Sub VisibleSpectrumValue() Dim c As Range For...

Pazi Can you post your file?

See attached

Do you want a summary like this: see attached:

Count of events is: =COUNTIFS($A$30:$A$58,"Recorded",$B$30:$B$58,"Pre-School 0-5") Attendance i : =SUMIFS($C$30:$C$58,$A$30:$A$58,"Recorded",$B$30:$B$58,"Pre-School 0-5")

This works for 2 case: =COUNTIFS($A$30:$A$58,"Recorded",$B$30:$B$58,"Pre-School 0-5") Can you clearly tell us what the 3rd case is ? as it is unclear above

Have you had a look at: https://www.lejavbasolutions.com/primes/ or http://www.vbaexpress.com/forum/showthread.php?61167-sieve-of-eratosthenes-vba-code-explanation

F3: =IF(OR(C3=0,D3<=0.001),0,E3/D3) copy down

try: '--------------------------------------------------- 'Highlight the current cursor paragraph '--------------------------------------------------- With TextBoxParagraph .SetFocus .SelStart = InStr(comstr, ans) - i - 1 .SelLength = Len(ans) - i - 1 .SetFocus End With

Don As far as I know, you can't control the formatting of a VBA TextBox's text, other than as a whole. You can use other VBA Controls to hold the text, that will allow that functionality eg: a Rich Text or HTML Box

I used: Private Sub CommandButton2_Click() Dim ans As String Dim MyStr As String MyStr = TextBoxParagraph.Text x = 1 'MyStr = Replace(MyStr, Chr(10), Chr(234)) 'MyStr = Replace(MyStr, Chr(13), Chr(234)) Dim SplitStr As String SplitStr = Chr(13) + Chr(10) + Chr(13) + Chr(10) Debug.Print MyStr...

@Peter For one off jobs, Flash Fill has became my goto tool, 99% of the time it is very clever and can extract quite complex data patterns Fixing up a few outliers is normally quicker than a custom formula But I still use Formula's especially for more repetitive jobs