how to order multiple copies in other sheet into group/under a created header/row automatically?
With the code below, I want to order every time I copy to the other sheet to be organized like this.
Instead the code does this:
How can I change it?
Set findfirst = targetSheet.Range("H:H").Find("Tykkelse [m]") currentvalue = findfirst.Offset(1, 0).Value findfirst.Offset(-1, 0).EntireRow.Insert xlDown With Range(Cells(findfirst.Row - 2, 1), Cells(findfirst.Row - 2, 14)) .Merge .HorizontalAlignment = xlCenter .Interior.ColorIndex = 27 .Font.Bold = 1 .Font.Size = 18 .Value = currentvalue End With
See also questions close to this topic
Using VBA, print an array made in Word to Excel
I am a VBA novice and I am trying to print an array that I was able to make (basically copying from another post) in VBA today. I placed a break into the script and inspected the array in the locals page to see that the array captures what I want (and some extra data that I will filter out). I spent the day reading about printing arrays on stack overflow and other sites and I ended up a bit lost. My goal is to export the array as a table in excel.
The script looks for underlined sentences in a 400 page word document and places them into the array. All that's really necessary for printing is the underlined sentences, so maybe an array wasn't the best approach? How can I export the array 'myWords' to a fresh excel document or one that I designate?
Many thanks for your help!
Sub addUnderlinedWordsToArray() On Error GoTo errhand: Dim myWords() As String Dim i As Long Dim myDoc As Document: Set myDoc = ActiveDocument ' Change as needed Dim aRange As Range: Set aRange = myDoc.Content Dim sRanges As StoryRanges: Set sRanges = myDoc.StoryRanges Dim ArrayCounter As Long: ArrayCounter = 0 ' counter for items added to the array Dim Sentence As Range Dim w As Variant Application.ScreenUpdating = False ReDim myWords(aRange.Words.Count) ' set a array as large as the ' number of words in the doc For Each Sentence In ActiveDocument.StoryRanges For Each w In ActiveDocument.Sentences If w.Font.Underline <> wdUnderlineNone Then myWords(ArrayCounter) = w ArrayCounter = ArrayCounter + 1 End If Next Next Set myDoc = Nothing Set aRange = Nothing Set sRange = Nothing Application.ScreenUpdating = True Exit Sub errhand: Application.ScreenUpdating = True MsgBox "An unexpected error has occurred." _ & vbCrLf & "Please note and report the following information." _ & vbCrLf & "Subroutine Name: addUnderlinedWordsToArray" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description: " & Err.Description _ , vbCritical, "Error!" End Sub
High lighting 2 cells in Excel if value on another sheet is greater/less than either of the values
I have 2 cells on one sheet in excel that pull in external data. The higher value is in the top cell and the lower value in the lower cell. I want to highlight both cells if a third value on another sheet is greater than the high value or lower than the lower value on the first page.
I have tried using conditional formatting by selecting both cells and making 2 different rules for those 2 cells but it doesn't seem to work. I have tried using the OR function and only making one rule but that hasn't worked either.
Any ideas on the best way to do this? I want both cells to be highlighted.
Apply same format to different cell with different input
I would like to know, if possible, how can the following be done: I have two cells with different inputs that change depending on filters.
A1 = 5 A2 = 7
A1has a dynamic format that changes depending on its value.
The value of
A2also changes, however I would like to have the same cell color and font as
A1despite not having the same number.
Copy formatting is not an option as it is static. Apply same formatting rules does not do the work as it has different values.
Maybe a macro that copies format of
A2every time change in value happens.
thanks in advance,
Access Vba can't find the Syntax error in Insert Into
I'm writing the following VBA code for a button in an Access form. I want the information inserted by the user to add a new record to a specific table, the table Cliente.
Private Sub novo_cliente_Click() Dim Id_Cliente As Integer Dim Primeiro_Nome As String Dim Ultimo_Nome As String Dim NIF As String Dim E_Cliente As String Dim Sexo As String Dim Contacto As String Dim Endereco As String Dim Localidade As String Dim Data_Nascimento As Date Dim Email As String Id_Cliente = Me.Id_Cliente.Value Primeiro_Nome = Nz(Me.Primeiro_Nome.Value, Empty) Ultimo_Nome = Nz(Me.Ultimo_Nome.Value, Empty) NIF = Nz(Me.NIF.Value, Empty) E_Cliente = Nz(Me.E_Cliente.Value, Empty) Sexo = Nz(Me.Sexo.Value, Empty) Contacto = Nz(Me.Contacto.Value, Empty) Endereco = Nz(Me.Endereco.Value, Empty) Localidade = Nz(Me.Localidade.Value, Empty) Data_Nascimento = Nz(Me.Data_Nascimento.Value, Empty) Email = Nz(Me.Email.Value, Empty) 'If IdCliente, NIF e e_Cliente empty If IsNull(Me.Id_Cliente.Value) = True Or IsNull(Me.NIF.Value) = True Or IsNull(Me.E_Cliente.Value) = True Then MsgBox "Please insert data in the required fields", vbExclamation, "Warning" Else DoCmd.RunSQL "INSERT INTO Cliente (Id_Cliente, Primeiro_Nome, Ultimo_Nome, NIF, Cliente?, Sexo, Contacto, Endereco, Localidade, Data Nascimento, Email) VALUES (Id_Cliente,'" & Primeiro_Nome & "', '" & Ultimo_Nome & "', '" & NIF & "', E_Cliente, Sexo, '" & Contacto & "', '" & Endereco & "', '" & Localidade & "', Data_Nascimento, '" & Email & "')" Me.Id_Cliente.Value = Empty Me.Primeiro_Nome.Value = Empty Me.Ultimo_Nome.Value = Empty Me.NIF.Value = Empty Me.E_Cliente.Value = Empty Me.Sexo.Value = Empty Me.Contacto.Value = Empty Me.Endereco.Value = Empty Me.Localidade.Value = Empty Me.Data_Nascimento.Value = Empty Me.Email.Value = Empty End If End Sub
Right now, I'm getting a syntax error in INSERT INTO statement, but I cannot find the error. Is it in "Cliente?" ? Thank you.
How do I get an Outlook rule to only run a script on the most recent email?
I've written an Outlook script to extract an attachment and save it to my server. I've also created a rule in Outlook to only apply this script to emails with a specific subject line.
However, these emails are daily reports with the same subject line. Whenever I attempt to run this rule, it takes a report from any given day with that subject line and saves that attachment, rather than the most recent report.
Is there any way to fix this in outlook, or even in the script I'm using? I appreciate all the clarity I can gain on this issue!
Public Sub SaveAttachmentsToDisk4(MItem As Outlook.MailItem) Dim oAttachment As Outlook.Attachment Dim sSaveFolder As String sSaveFolder = "K:\BI and Information\Farah\" For Each oAttachment In MItem.Attachments oAttachment.SaveAsFile sSaveFolder & oAttachment.DisplayName Next End Sub