[ad_1] You have a red exclamaition mark on your project. Eclipse is likly to not build your project if build path problems (e.g.) aren’t resolved. So go to the Problems tab and try to resolve your errors there. If that is finished Eclipse will build your project and gives more helpful errors Edit and that … Read more
[ad_1] In VBA, there is the AscW function that returns the Unicode code point for the first letter of a string as a decimal number. Use the following code snippet as an example: Sub mycode() MsgBox “female symbol code = ” & CStr(AscW(Cells(1, 1).Text)) & “, male symbol code = ” & CStr(AscW(Cells(2, 1).Text)) End … Read more
[ad_1] If you want the name in column E and the worked hours in column F then set E1 to =IF(B1>0,A1,””) and set F1 to =IF(B1>0,B1,””) If you want the name and worked hours both in column E then set E1 to =IF(B1>0,CONCATENATE(A1,” “,B1),””) and copy down the column. IF(condition, expression if true, expression if … Read more
[ad_1] Count of Shift Combinations within a List of 100 continuous Shifts This answer is a solution to the revised requirements of the asker. I’m posting this answer as the asker changed the original requirements, and therefore requiring a different solution than the first posted. I decide to leave both solutions for the benefit of … Read more
[ad_1] First, This will not paste any headers. Since they are all the same, just move the headers to the excel that will store the consolidation. (No need for a macro to do this since it only needs to happen once). Second, in the code you need to change “SHEETNAME?” to the name of the … Read more
[ad_1] If I am seeing what you are trying to do, it shouldn’t be too hard. You could hardcode a for loop to 800. for i = 2 to 800 Range(“A”&i).Copy Destination:=Sheets(i).Range(“A” & i) next This is similar albeit a bit more involved [ad_2] solved In main sheet there is 800 names(A1:A800). Each cell should … Read more
[ad_1] It seems that in the documentation of xlwings, it is necessary to have an empty row and column at the bottom and to the right. if not it will overwrite it http://docs.xlwings.org/en/stable/api.html#xlwings.xlwings.ret [ad_2] solved xlwings udf function erase next cell on excel
[ad_1] Try this code, but before you do adjust the two constants at the top to match the facts on your worksheet. The worksheet with the data must be active when the code is executed. Sub TransposeData() Const FirstDataRow As Long = 2 ‘ presuming row 1 has headers Const YearColumn As String = “A” … Read more
[ad_1] Please never use reserved words like list, type, id… as variables because masking built-in functions. If later in code use list e.g. list = data[‘FirstNames’].tolist() #another solution for converting to list list1 = list(data[‘SecondNames’]) get very weird errors and debug is very complicated. So need: L = data[‘FirstNames’].tolist() Or: L = list(data[‘FirstNames’]) Also can … Read more
[ad_1] Use the next function, please: Function newID(sh As Worksheet) As String Dim lastR As Long, strID As String, arr As Variant, i As Long lastR = sh.Range(“A” & Rows.count).End(xlUp).Row arr = sh.Range(“A2:A” & lastR).Value strID = left(sh.Range(“A2”).Value, 4) For i = 1 To UBound(arr) arr(i, 1) = CLng(Right(arr(i, 1), 3)) Next i newID = … Read more
[ad_1] Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function¹ and the updated CONCAT function² have difficulty with conditional concatenation beyond TEXTJOIN’s blank/no-blank parameter. Here are a pair of User Defined Functions (aka UDF) that will perform the tasks. Function udfUniqueList(rng … Read more
[ad_1] So is it a text file or excel file you are importing? Without more details, file structure, or a reproducible example, it will be hard to help. You can try: # Get the correct package install.packages(‘readxl’) df1 <- readxl::read_excel(‘[directory name][file name].xlsx’) df2 <- readxl::read_excel(‘[directory name][file name].xlsx’) # Creates a new variable flag if miRNA … Read more
[ad_1] The comments actually helped me get the answer by pointing me to openpyxl. I’m posting it here if anyone else had it. import openpyxl wb = openpyxl.load_workbook(‘Roster Report.xlsx’) header_text = str(wb.active.HeaderFooter) wb.close() I didn’t see a way in xlrd to read the header, only to write it. 2 [ad_2] solved Read Excel Text Header … Read more
[ad_1] Something like this might work in the ThisWorkbook module’s, Workbook_Open event. Private Sub Workbook_Open() Dim ws As Worksheet Dim mnth As String, dte As String, mday As String mday = Now() – Weekday(Now(), 3) mnth = Month(mday) dte = Day(mday) tabstr = mnth & “-” & dte For Each ws In Worksheets If ws.Name … Read more
[ad_1] Collect the data into a variant array Process the collation within the array backwards Dump the data back to the worksheet Remove duplicates based on the first column. Code: Option Explicit Sub Macro1() Dim i As Long, j As Long, arr As Variant With Worksheets(“sheet10”) ‘Collect the data into a variant array arr = … Read more