Very well presented. This is the first of the tutorial that explained array in an uncomplicated way. Thanks very much. Can you make a Search Module tutorial using UserForm with multiple condition and output the result in a listbox. An example application system for tracking employee training where a company has more than 20 types of health & safety training. 1st search condition is employee lastname or employee ID, 2nd condition is training description and 3rd search is status of certification (expired: soon to expire:active). Hope you can provide some guidance. Thanks a lot.
@sircorn4248
3 жыл бұрын
Many thanks. Excellent video, solves my problem
@LoveCodingAndPlay
3 жыл бұрын
Glad it helped
@christophseyfarth8018
4 жыл бұрын
Great Video! Trank you very much!
@sivasubramanian4626
Жыл бұрын
Great video thank you so much ❤❤❤
@TylehurstXL
4 жыл бұрын
Wow, great video. Your Vba writing speed is amazing. I do similar but try using dictionary object to save row number of every fname lname combination. Cleaner and less writing
@jeannoelaction
4 жыл бұрын
Bravo good job
@petralabui9302
4 жыл бұрын
your video is amazing
@LoveCodingAndPlay
4 жыл бұрын
thank you
@ryancagulang7543
4 жыл бұрын
Its working for me too. Thanks for the video
@olatundeopadokun6040
3 жыл бұрын
This is great.....I will like to share my worksheet with you(am new to VBA), am trying to Map columns headers on 2 different sheets(rawdata sheet and clean sheet) before coyping the columns rows. Will appreciate your feedback on this.
@yazihernandez5166
Жыл бұрын
Doesnt really work as fast for mine tho, any improvement i can make? I have data that has equivalent to 7k rows and 30 columns. takes like a min to load..
@vijaykumar-wg4pg
4 жыл бұрын
Hi Jay, Thanks for your video. Can you please do a video on RFM using VBA arrays. Thanks, Vijay
@LoveCodingAndPlay
4 жыл бұрын
What is RFM?
@vijaysahal4556
3 жыл бұрын
nice but we cnt properly understand bcz it was big data of array if you cn same array programs with small data then you can help with us👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
@redhaakhund1271
3 жыл бұрын
You are master and best of the best every seen👍👍👍👍👍. thank so much. i have subscribed your channel. what if you have a list of employees, date and status in sheet1. if employee status is "" then copy/move to and on the the employee whos status is will be taken his duty/place. Waiting your video soon.
@LoveCodingAndPlay
3 жыл бұрын
That should be easy even without using an Array, sure I'll plan a great video for that. Thanks for the supportsf
@phucnguyenlx
4 жыл бұрын
Hi Jay, for the SEARCH DATA sheet. When I start the column from "F" not "A", I did as below but it gave does not return anything. Could you please advise? Tks Sub array_Match_Data() Debug.Print Format(Now, "hh:mm:ss") Dim rSH As Worksheet Dim sSh As Worksheet Set rSH = ThisWorkbook.Sheets("RAW DATA") Set sSh = ThisWorkbook.Sheets("SEARCH DATA") Dim rawArray() As String Dim searchArray() As String ReDim Preserve rawArray(1 To rSH.Range("A" & Rows.Count).End(xlUp).Row, 1 To 9) ReDim Preserve searchArray(1 To sSh.Range("F" & Rows.Count).End(xlUp).Row, 1 To 7) For a = 1 To rSH.Range("A" & Rows.Count).End(xlUp).Row For b = 1 To 9 rawArray(a, b) = rSH.Cells(a, b) Next b Next a For a = 1 To sSh.Range("F" & Rows.Count).End(xlUp).Row For b = 1 To 7 searchArray(a, b) = sSh.Cells(a, b) Next b Next a Dim fName As String For a = 1 To UBound(searchArray) fName = searchArray(a, 1) For b = 1 To UBound(rawArray) If rawArray(b, 1) = fName Then searchArray(a, 2) = rawArray(b, 7) searchArray(a, 3) = rawArray(b, 8) searchArray(a, 4) = rawArray(b, 9) Exit For End If Next b Next a 'Transfer data back For a = 2 To UBound(searchArray) For b = 2 To 7 sSh.Cells(a, b).Value = searchArray(a, b) Next b Next a Debug.Print Format(Now, "hh:mm:ss") Debug.Print "Process Completed" End Sub
@kamalakannan.1971
3 жыл бұрын
If the search sheet had data starting at column F you need to add 5 to the column index b in the two loops. See belo Sub array_Match_Data() Debug.Print Format(Now, "hh:mm:ss") Dim rSH As Worksheet Dim sSh As Worksheet Set rSH = ThisWorkbook.Sheets("RAW DATA") Set sSh = ThisWorkbook.Sheets("SEARCH DATA") Dim rawArray() As String Dim searchArray() As String ReDim Preserve rawArray(1 To rSH.Range("A" & Rows.Count).End(xlUp).Row, 1 To 9) ReDim Preserve searchArray(1 To sSh.Range("F" & Rows.Count).End(xlUp).Row, 1 To 7) For a = 1 To rSH.Range("A" & Rows.Count).End(xlUp).Row For b = 1 To 9 rawArray(a, b) = rSH.Cells(a, b) Next b Next a For a = 1 To sSh.Range("F" & Rows.Count).End(xlUp).Row For b = 1 To 5 searchArray(a, b) = sSh.Cells(a, 5 + b) Next b Next a Dim fName, lName As String For a = 1 To UBound(searchArray) fName = searchArray(a, 1) lName = searchArray(a, 2) For b = 1 To UBound(rawArray) If rawArray(b, 1) = fName And rawArray(b, 2) = lName Then searchArray(a, 3) = rawArray(b, 7) searchArray(a, 4) = rawArray(b, 8) searchArray(a, 5) = rawArray(b, 9) Exit For End If Next b Next a 'Transfer data back For a = 1 To UBound(searchArray) For b = 3 To 5 sSh.Cells(a, 5 + b).Value = searchArray(a, b) Next b Next a Debug.Print Format(Now, "hh:mm:ss") Debug.Print "Process Completed" End Sub
@mikedeboer
2 жыл бұрын
This code doesn't work even before you put in the loop.
@Ram__o9
2 жыл бұрын
Could we use arrays in another workbook if our raw data in another workbook and search data in another workbook
@LoveCodingAndPlay
2 жыл бұрын
I think the excel query is much helpful for that scenario instead of manipulating data thru array
@Ram__o9
2 жыл бұрын
@@LoveCodingAndPlay thanks!!
@loidaasar487
4 жыл бұрын
i hope you can send the practice file so i can practice along
Пікірлер: 26