Posts by zsolti90

Hello Everybody,

I would like ask for same help with serial communication. I did build my aruino project and i would like send cell value from excel to arduino with serial communication on COM port.
I did find few example, but my main problem is the speed. When i send data from excel to arduino on COM port, need around 1-1.5sec do the task which is too long time. I think because before every data send rebuild the connection between arduino and PC.

Can i ask how can i solve this problem? I would like find jsut something simple code, because i have to send just 1 cell value. I attached the file what i try used.

Soembody have maybe idea how can i speed up littlebit? If i can reach just 0.5sec communication this can be perfect.

Thank you.

Yes! Tank you very much. its work very well. Really thank you your help!

Yes, excatly what i would like to do. This is just something extra, i dont know its possible or not, But if i remove the value the highlight as well should be remove. But if its not posible, more than enough for me, if get value in D column jsut highlight that row. Which is empty its have to stay without highlight.

yes sure. please see below. i would like autohihglight that row where is value in D column.

Is it possible to do something, if i have value in C:10 highlight this row?

I deleted it, because i tried use with other code. So that message nothing mean now.

I should integrate this to my code, but the problem is for me thsi code not work, and you have fix file name for 2.xlsx.

I try run run jsut this code but nothing happen. I dont know where is the problem because i didnt got any errors.

I did try test it, but i got "For control variable already in use error"

I did try change the macro but the highlight not work very well, because this code highlight the empty cell as well. Please see below my code:

Option Explicit
Function getFile() As Workbook
 Dim fn As Variant
 
 fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select workbook")
 If TypeName(fn) <> "Boolean" Then Set getFile = Workbooks.Open(fn)
 
 
End Function



Sub useGetFile()
 Dim Dic As Object
 Dim key As Variant
 Dim oCell As Range
 Dim i As Long
 Dim wb1 As Workbook, wb2 As Workbook
 Dim ws1 As Worksheet, ws2 As Worksheet
 
 Set Dic = CreateObject("Scripting.Dictionary")
 
 Set wb2 = getFile
 If Not wb2 Is Nothing Then
 On Error Resume Next
 Set ws2 = wb2.Sheets("Sheet1")
 On Error GoTo 0
 If Not ws2 Is Nothing Then
 Set wb1 = Workbooks("1.xlsx")
 Set ws1 = wb1.Sheets("Sheet1")
 i = ws1.Cells.SpecialCells(xlCellTypeLastCell).Row
 For Each oCell In ws1.Range("A1:A" & i)
 If Not Dic.exists(oCell.Value) Then
 Dic.Add oCell.Value, oCell.Offset(, 3).Value
 End If
 Next
 i = ws2.Cells.SpecialCells(xlCellTypeLastCell).Row
 For Each oCell In ws2.Range("A2:A" & i)
  
 
 For Each key In Dic
 If oCell.Value = key Then
 oCell.Offset(, 3).Value = Dic(key)
 oCell.Offset(, 0).Interior.ColorIndex = 37
 oCell.Offset(, 1).Interior.ColorIndex = 37
 oCell.Offset(, 2).Interior.ColorIndex = 37
 oCell.Offset(, 3).Interior.ColorIndex = 37
 oCell.Offset(, 4).Interior.ColorIndex = 37
 oCell.Offset(, 5).Interior.ColorIndex = 37
 oCell.Offset(, 6).Interior.ColorIndex = 37
 oCell.Offset(, 7).Interior.ColorIndex = 37
 End If
 Next
 Next
 Else
 MsgBox "Sheet1 not found in " & wb2.Name, vbCritical
 End If
 'Maybe close wb2 here?
 'wb2.Close SaveChanges:=False
 Else
 Debug.Print "User cancelled"
 End If
 Set wb1 = Nothing
 Set wb2 = Nothing
 Set ws1 = Nothing
 Set ws2 = Nothing
 Set Dic = Nothing
End Sub

In 1.xlsx i have a massive list which is couple of thousand lines. In 2.xlsx file i have like 5-10 or sometimes 30 lines. So i put the value from 1.xlsx to 2.xlsx, after than i would like highlight the row where is the new value.

Yes i have macro in 1.xlsx, which automatic put the value from 1.xlsx to 2.xlsx, but i would like highlight as well. So yes the 1. file form is 1.xlsm, but i can put other button wich highlight button and its can highlight in 2.xlsx file row.

2.xlsx file we create this, we put same number from email, and after we have search in 1.xlsx file the value for number, but this part done with macro, i would like just highlight this rows.

I work with opened files. I hope i explain to you everything. Thank you your help!

Thank you very much your help! I have just one problem. Later i have to use the 2.xlx file to run script in other app, thats why sadly i can not change the format to .xlsm from .xlsx. Possible to do it from 1.xlsx? Its really big help for me!

Sorry i forget said to you 2 more things. Can be couple of thousand line, and in 2.xlsx file can be lines with value in "C" whoch different than 1.xlsx.

Yes you right about it, so i have 2 files. The first name always fix, but the second file always change, i wouldnt like change in code each time when i want run the macro.

In 1.xlsx file have lot of lines, if i have same line in 2.xlsx i would like highlight,

Hi! I would like ask, how possible do in VBA highlight row if specific cell not empty.
For example i have numbers in "A" column 1,2,3,4,5 and if have value next to 1,3 and 5 in "B" column like: 1 | a , 3 | b, 5 | c highlight this 3 row from "A" to "C".

OMG its work. Thank you very much!

I tested and its work. Thank you really your help! I have jsut one problem. Its can not copy the cells to other excel file.

Can you help me how can i fix it? I am on really basic level with VBA

Thank you i test it now

This line:

Dic.Add oCell.Value, oCell.Offset(, 3).Value

I did try but i got "Object variable or With block variable not set" error.

Thank you very much your help. I did something like this, but sadly its not work i get always error:

Option Explicit
Function getFile() As Workbook
 Dim fn As Variant
 
 fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select workbook")
 If TypeName(fn) <> "Boolean" Then Set getFile = Workbooks.Open(fn)
End Function
Sub useGetFile()
Dim Dic As Object, key As Variant, oCell As Range, i&
 Dim wb1 As Workbook, wb2 As Workbook
 Dim wb1Sheet1 As Worksheet, wb2Sheet1 As Worksheet
 
 Set wb2 = getFile
 If Not wb2 Is Nothing Then
 On Error Resume Next
 Set wb2Sheet1 = wb2.Sheets("Sheet1")
 On Error GoTo 0
 If Not wb2Sheet1 Is Nothing Then
 Set wb1 = Workbooks("1.xlsx")
 Set wb1Sheet1 = wb1.Sheets("Sheet1")
 i = wb1.Cells.SpecialCells(xlCellTypeLastCell).Row

 For Each oCell In wb1.Range("A1:A" & i)
        If Not Dic.exists(oCell.Value) Then
            Dic.Add oCell.Value, oCell.Offset(, 3).Value
        End If
    Next

    i = wb2.Cells.SpecialCells(xlCellTypeLastCell).Row

    For Each oCell In wb2.Range("A2:A" & i)
        For Each key In Dic
            If oCell.Value = key Then
                oCell.Offset(, 2).Value = Dic(key)
            End If
        Next
    Next
    
    
 Else
 MsgBox "Sheet1 not found in " & wb2.Name, vbCritical
 End If
 'Maybe close wb2 here?
 wb2.Close SaveChanges:=False
 Else
 Debug.Print "User cancelled"
 End If
 Set wb1 = Nothing
 Set wb2 = Nothing
 Set wb1Sheet1 = Nothing
 Set wb2Sheet1 = Nothing
End Sub

Hello! I have this code:





Set w1 = Workbooks("1.xlsx").Sheets("Sheet1")

Set w2 = Workbooks("2.xlsx").Sheets("Sheet1")



My poblem is, every time the 2.xlsx file name change, and i would like change it each time, possible to do with button, just browse the file? Its something simple code just copy few cells after if its match with the 1.xlsx file. Th 1.xlsx file can be fix because for this file never change the name, but the 2.xlsx filename always can be anything. I dont have too much experience with VBA, and its totally different than C# so i stuck here.



Somebody can help me please, how possible give for w2 file name and path with button? Thank you!